Shareware Grab Bag

home *** CD-ROM | disk | FTP | other *** search

/ Shareware Grab Bag / Shareware Grab Bag.iso / 096 / rbnotes1.arc / RBASE1.NOT

Wrap

Text File | 1986-11-06 | 160KB | 4,101 lines

SDF , NEWS FROM TECH SUPPORT, #1 ----------------------------------- This is a new feature. Rather than having you sort thru all the messages to find the new info, we will post it here first and then put it in the message section later. We will be trying to keep you up to date on the latest and greatest ideas, tips, problems, workarounds, etc. as they happen. IMPORTANT: We are trying to establish how effective this board is to users. Please leave a message with your feelings about it in the General area of the message section. Your input is vital to the maintenance and improvement of this board. 1) 2 new books about R:base products: -Using R:base 4000 by J. Erickson and N. Baran publisher: Osborne Mcgraw-Hill -The Book of R:base by G. Roeder publisher: All-Hands-On Press 2) Filegateway: When transferring data from Lotus, many people have commented that sometimes integer data in Lotus comes thru as real data in R:base. The reason for this is that Lotus allows 16 bits for storage of an integer value, 32000 is the largest integer that can be stored. Over this, the number is stored internally by Lotus as a real value, even though it is displayed on the screen as an integer. Use the following guidelines: numbers <= 32000 : will be transferred as integer unless there is a decimal point and a number past the decimal. numbers > 32000 : will be transferred as real Also remember that Filegateway will look at ALL data values in the incoming file before assigning the data type, so even if there is only one value > 32000, it will be assigned to a real data type. 3) If you are running on a hard disk, but you have to boot it from a floppy disk, it can be annoying when it looks to the floppy drive for command.com when you exit a program. To avoid this, put the following command in your config.sys file on the floppy booter, and put a copy of command.com on the root directory of your hard disk. SHELL=C:\COMMAND.COM C:\ /P 4) If you are doing string concatenation (ie firstnm & lstnm), if the total number of characters is the same for two consecutive rows, a character from the first row will drop into the space in the second row. To fix this, there are two things you can do: 1) break on firstnm and reset the concatenated variable. 2) outside report writer, set a variable "space" to "alt 255" (alt 255 is the decimal value of the ascii blank) Inside report writer, define the following variables: x1 = firstnm + .space name = x1 + lastnm 5) Reports - how to print a grand total with no detail. Mark your report with report headers and report footers, and locate your grand total on one of the footer lines. It is not possible to do this procedure without a break of some kind, so what you can do is set a report variable to a constant (ie v1 = 3) and set a break level on that variable. Don't mark any break headers or footers and don't reset any variables. 6) When running an application created in Express, many people have mentioned that the rules they had defined were not being displayed on the screen. If the rule was violated, the machine would beep, but no error message would be displayed. This is because Express sets the error messages off and messages off, and it thinks of these messages as error messages. All you need to do is insert a custom in Express and set the error messages on before loading data, and set them off again when you're all finished loading (or editing) data. 7) Time arithmetic: the only form of time arithmetic that you can do is time - time = integer (# of seconds). 8) This was not a documented feature in R:base 4000, but if you hit the F2 key during data entry or editing using forms, it would clear every- thing to the right of the cursor. This is not available in R:base 5000 because they are using F2 for other functions. 9) XRW - If you are using DOS 3.1, XRW will not output to the printer. You will get an error that says there is an error creating the output file. Workaround: output to a disk file, and use the DOS print command to print the file to the printer. 10) In command files, you can use variables as the row, column specifiers in "at 11,12" statements. ie "at .line,.clmn". This is very useful for controlling screen positions. 11) After loading R:base 5000, if you try to open a database and get the error "out of file handles" and "unable to open file", it means that you did not properly configure your system. You need to have a config.sys file on your root directory or boot disk, and reboot your machine afterwards. (config.sys should say files=20) 12) If you are trying to convert from R:base 4000 to R:base 5000, and your convert program seemed to run very quickly and gave the message "conversion complete", but it didn't seem to do anything, it is probably because the database was not open. What has happened in many cases is that people do not properly configure their system (config.sys -->files=20) and when the program tries to open the database, it gives an error -out of file handles- unable to open database. However, messages are set off so you never see it. You need to go back and configure your system properly and then run the convert program one more time. tech notes 2 NOTES FROM TECH SUPPORT, #2 --------------------------------- Notes from Sysop: -Instead of converting all these items to messages, I am going to be putting each editorial file in Files area, Demos section. They will be numbered, so if you call and find out that you missed one, you can download or type out the other editorials. -A HUGE thank-you to all of you who are leaving messages supporting this BBS! (pssst - I think it's working!) -Just wanted to let you know that we will be undergoing some changes in the operating procedures of this BBS from our end in the next few weeks. Hopefully you won't notice any changes, but if you do, please bear with us until we get our new people and procedures in place. 1) IBM Enhanced Color Graphics appears to work with R:base 5000. 2) When to use variable forms instead of table forms: - whenever you wish to input data from one form into more than one table. - when multi-page forms are desired - when you want to display some data on a data entry form that cannot be edited - when "lookups" are desired ie - you want the operator to enter the customer number and R:base to display the customer's name, address, etc.) - when on-screen calculations are needed - when you want to load information from the screen into variables instead of directly into a table 3) How to clear a portion of the screen when using variable forms: - A) create a "blank" form. To do this, you must use the edit mode in forms to put an "alt 255" in the upper left and lower right corners of the form. By doing this, the form appears blank even though it actually has two characters on it. (Variable forms must have some characters on it, it can't be completely blank). You can then draw this form at any particular point on the screen. The only limitation is that you can only "draw" five times before doing a "newpage". - B) Another way to clear a portion of the screen is to use the "write" command. write " (40 blanks) " at 11,1 write " (40 blanks) " at 11,40 and repeat for each line you want cleared - C) You can also use the "write" command to write 80 blanks: write " ........(lots of blanks - total of 80).......+ ......" at 11,1 - D) To clear several lines, use the method described in C) within a while loop: set var line to 11 while line lt 24 then write " (the 80 blank method described in C) " at .line,1 set v line to .line + 1 endw - E) This method involves more typing, but it appears to be a little quicker than method D). Use the method described in C), but repeat each write statement for each line you want cleared: write " (80 blanks) " at 11,1 write " (80 blanks) " at 12,1 write " (80 blanks) " at 13,1 etc., etc. - F) The last method appears to be the fastest of all. It involves the use of the "alt 255" special character. You use one "write" statement with as many lines as needed of alt 255's. write "....(use your F4 repeat key to repeat an alt255).... ........................................................... ........................................................... ........do this for how ever many lines you want to clear.. .........." at 11,1 4) To select all rows for a particular month, day, or year, use wildcards. The only rule for wildcards with dates is that you must include the delimiters: ...where tdate = */14/* ...where tdate = */*/85 ...where tdate = jun/*/* ...where tdate = "jun 14/*" ...where tdate = */*/85 ...where tdate = */*/1985 5) If you are a consultant or VAR and would like to have your name in our consultant list, please write a letter to that effect to: VAR/Consultant Manager Microrim, Inc. 3380 - 146th PL SE Bellevue, WA 98007 6) XRW - We have a reported workaround for the problem of not being able to DOS output a report to the printer (you have to output to a file and then 3.1 use the DOS print command to send it to the printer). When prompted for the output device, type in PRN: instead of PRINTER. 7) If you are having "strange" problems with programs hanging during loading or in some cases during operation, check to see if you have any programs installed in memory. In almost all cases, if you remove these memory-resident programs, the R:base programs (Express, Gateway, etc.) will work fine. 8) A few notes about the operation of this board. - If you try calling and it rings and rings and never answers, it is because I am on. - If you want to escape out of reading the logon files (new product info, etc.) hit ^C. 9) Reading the answers to the questionnaire question about making the BBS user list available, it appears that you are split just about even. Therefor, to avoid any conflict, it will be voluntary. If you leave your name, city, st, phone (optional), and what kind of applications you're into, I will put this information in a text file and put it in one of the Files areas. I would encourage you to become involved in this because a great deal of valuable infor- mation is available from other users working on the same type of application as you. 10) A user has very generously contributed a utility that allows you to run R:base 4000 in color. I am putting it in the DEMOS section of the Files area. We have not done extensive testing on it, but he tells me he hasn't had any major problems with it. 11) There is a new version of LABELS.MAC called NEWLAB.MAC in the Applic area of the Files section. There is also another method for doing many-up lables called LAB2.CMD, which uses an altogether different approach. tech notes 3 ****************************************** NOTES #3 FROM TECH SUPPORT - JULY 12, 1985 ****************************************** We will be putting new issues of this file on every 2 or 3 weeks and will be rotating the authorship among all the TECH SUPPORT ANALYSTS so you will find a variety of writing styles. We will also be publishing much of this info in the R:base EXCHANGE. Back issues of these notes can be found in the FILES section. ************************** SAVING RESULTS OF COMMANDS ************************** It is often useful to save the results of some R:base commands in a table of their own. For example, you might want to save: o Subtotals and other calculated information from a report o The results of a tally command o The results of a directory (to keep a table of your files). This can be accomplished by sending output to a file and then loading the file into a previously defined table. For example, to store the results of a TALLY command you would: 1. set mess, err mess, and bell off 2. output to a filename 3. issue your TALLY command line 4. output terminal 5. load the file as ascii into your table (defined with 1 text column to hold the item and 1 integer column to hold the number of occurances. 6. check the first row and if it has garbage in it then delete it. Similarly, if you have 31 daily backup datafiles to be loaded into a table, you can do it without having to enter 31 commands at the keyboard. Simply copy the 31 files to your hard disk (making sure that they are the only files with a .BAK extension in the current directory) and use the following command file to do all the work overnight: OUT TEMPFILE.DAT DIR *.BAK OUT TERMINAL OPEN dbname DEFINE COL FILENAME TEXT 8 TAB FILES WITH FILENAME END LOAD FILES FROM TEMPFILE.DAT AS ASCII DEL ROWS FRO FILES WHE LIM = 2 DEL ROWS FRO FILES WHE COU = LAS SET VAR LIMIT INTEGER SET VAR COUNTER INTEGER COM LIMIT AS ROWS FROM FILES WHILE COUNTER LE .LIMIT THEN SET VAR CURFILE TEXT SET VAR CURFILE TO FILENAME IN FILES WHE COU = .COUNTER SET VAR CURFILE TO .CURFILE + ".BAK" LOAD tblname FROM .CURFILE AS ASCII SET VAR COUNTER = .COUNTER + 1 ENDWHILE The SET POINTER command was not used because in this case, where only one access is made to a particular row, SET POINTER will not save any time. ************************* BACK UP YOUR APPLICATIONS ************************* As a general rule we recommend that you backup all three of your application files before going into EXPRESS to change an application. Before erasing any of your application backups be sure to fully test your current version to ensure that it is completely operational. ************* EXPRESS QUIRK ************* If you have a column defined as TEXT with a length of 10 or more and you are entering only numbers into it, you may have trouble pulling up your record using only the EDIT option if you are requesting a value at execution time. The reason for this is that the EXPRESS does not currently allow you to put a TEXT datatype on the FILLIN variable used in the EDIT command. To avoid this problem, use RBEDIT on your .APP file to add the code necessary to TYPE the FILLIN variable. Remember that after using RBEDIT you should not go back and attempt changes with EXPRESS or your changes will be obliterated. After making the change, use RCOMPILE to make a new procedure file (the .APP file is the application file and the .APX file is the current procedure file - but you can give it any name you choose). An alternative workaround is to use EXPRESS but do not use the EDIT pick. Instead, use the CUSTOM pick and write the four lines of code yourself. Your code will be similar to the following: SET VAR WHVAL TEXT FILLIN WHVL USING "ENTER THE CODE NUMBER YOU WANT: " EDIT USING formname WHERE colname EQ .WHVAL CLEAR WHVAL ******************************* PROBLEMS WITH PRINTING REPORTS? ******************************* If you have problems when printing your reports it may be setting for the number of lines per page inside your report. Try putting the setting at 58 or 60 lines (using the SET option in the REPORTS command. Do not set it to be equal to the number of lines per page (usually 66) or extra pages may eject when printing your reports. In general your setting should be equal to the number of lines you want to print per page. **************************** SUBTOTALING WITH R:BASE 4000 **************************** Subtotaling with R:base 5000 can easily be accomplished by using the report writer. Subtotaling in R:base 4000 can be accomplished using the method described below. Customers often want to add up the balance on each of their accounts (all the invoices minus all the payments) and store it in a BALANCE field for each of the accounts. Here is a method. This method assumes that you have two relations - one (INVOICES) with the various invoice charges and payments in it (i.e., a transaction file) and a second relation (ACCOUNTS) with all the account numbers in it (i.e., the master relation). The ACCOUNTS master relation has only one record per account with all the information such as the account number, name, address, phone number, etc. and has one additional field BALANCE that holds the current balance for the account. The RUNBALS.CMD below is run every night to put the new balances into the master ACCOUNTS relation, thus always keeping the account balance information current. Admittedly, this is slow but it does work. LISTING OF RUNBALS.CMD: COMPUTE LIMIT1 AS ROWS FROM ACCOUNTS SET VAR COUNTER1 = 1 WHILE COUNTER1 LE .LIMIT1 THEN SET VAR VACCT# TO ACCT# IN ACCOUNTS WHE COUNT = .COUNTER1 SET VAR COUNTER1 TO .COUNTER1 + 1 COM LIMIT2 AS COU ACCT# FRO INVOICES WHE ACCT# = .VACCT# SET VAR COUNTER2 = 1 SET VAR BAL TO $0.00 WHILE COUNTER2 LE .LIMIT2 THEN SET VAR INV$ TO INVAMT IN INVOICES WHE + ACCT# = .VACCT# AND COUNT = .COUNTER2 SET VAR PAY$ TO PAYAMT IN INVOICES WHE + ACCT# = .VACCT# AND COUNT = .COUNTER2 SET VAR BAL TO .BAL + .INV$ SET VAR BAL TO .BAL - .PAY$ SET VAR COUNTER2 TO .COUNTER2 + 1 ENDWHILE CHA NEWBAL TO .BAL IN ACCOUNTS WHE ACCT# = .VACCT# ENDWHILE Voila!! there you have it. It works because WHE COU = n will find the nth occurance of the item meeting the rest of the where clause. This allows you to write one devil of a subtotaling command file. The only teeny little problem with this method is that it will not work with keys. You may, therefore, want to delete keys at the beginning of the procedure and then rebuild them again at the bottom. Also, do not forget that you do not want any NULL values in the INVAMT and PAYAMT fields, so you might want to use the CHANGE command before running the RUNBALS.CMD to change all null fields to zero. tech notes 4 ****************************************** NOTES #4 FROM TECH SUPPORT - JULY 26, 1985 ****************************************** This is issue number 4 in our series of bi-weekly technical tips. Back issues can be found in the FILES section. ******************************************** AUTOMATICALLY FILLING IN PORTIONS OF COLUMNS ******************************************** Using variable forms along with the string concatenation feature of R:base 5000 it is easy to automatically fill in portions of fields eliminating the need for operators to enter such things as: o Dashes in social security numbers. o Parentheses and dashes in telephone numbers. o Slashes in DATE columns. o Colons and seconds on TIME columns when all you have is hours and minutes. o Commas, decimal points, and/or dollar signs in numbers. All the operator need do is enter the appropriate numbers directly from the number pad. This can save alot of data entry time and improve the productivity of your application. At the same time the column is displayed on the screen with all the special characters to aid in reading and verifying accuracy. This feature is easily implemented using variable forms with the concatenations taking place before a record is loaded into the table with the LOAD command. This way you can also do all your error trapping before actually adding the record to the database. Use the following steps to implement this idea. 1. Using the FORMS command set up your variable form and locate a text variable for each portion of the column that you want to split into pieces leaving a space between the end of one (E) and the beginning of the next (S). Then go into edit mode and put the separation character between each of the pieces. For example, social security numbers have 3 pieces separated by dashes. You could name these variables VSSN1, VSSN2, and VSSN3. Now during data entry the operator will be bouncing over the dashes to enter the various pieces and will still be able to read the final result easily because the dashes will be on the screen. 2. Before loading the SSN into the data base you would want to concatenate the three pieces together by using the following commands: SET VAR VSSN TO .VSSN1 + "-" SET VAR VSSN TO .VSSN + .VSSN2 SET VAR VSSN TO .VSSN + "-" SET VAR VSSN TO .VSSN + .VSSN3 The variable to use in the LOAD command would be .VSSN. Any error trapping tests should be performed prior to loading VSSN to the database. Remember that RULES work only with table forms, not with variable forms. Using this same idea you can automatically insert commas, dollar signs, and/or decimals in long numeric fields when they are all going to have the same number of commas (e.g., they will all be over 1,000 but under 1,000,000). Again this allows the best of both worlds - easy readability along with fast data entry. ************************************************** WILDCARDS, THE "NOT CONTAINS" CONSTRUCT, AND RULES ************************************************** In most places, other than RULES, where you want to say DOES NOT CONTAIN textstring, simply use the following where clause: ...WHERE colname NE *textstring*... where textstring means any string of characters from 1 to 1500 long and colname has a TEXT datatype. Using wildcards in RULES can be interesting. In RULES the asterisk and the question mark are sometimes taken as literal characters rather than as wildcards when an operator other than CONTAINS is used in the condition. After using some commands, however, RULES may start using the asterisks as wildcards. We are currently researching this but in the mean time it is best not to use the wildcards in your RULES. The one exception is when you want to check for the existance of certain valid characters in certain positions in a column. This can easily be done with RULES. In order to do this you need to adhere to the following requirements: o The column you are checking must be TEXT. o The operator used in the condition must be CONTAINS. o To check certain postions, use the question mark. For example, the following rule checks for dashes in the correct position in social security numbers (SS#): "Invalid SS number" SS CON ???-??-???? This next rule checks for an X in position 2. "Invalid product code" PRODID CON ?X* We have had several other interesting experiences with wildcards in R:base 5000 version 1.0 that we want to share with you. Because of the global nature of wildcards it would be a good idea not to use literal asterisks and/or question marks in your data. You could get into trouble later with commands like the following: 1. DEL ROWS FROM tblname WHE colname CONTAINS * 2. DEL ROWS FROM tblname WHE colname CONTAINS "*" The first command above will not delete rows even if the colname does contain an asterisk. Likewise the SELECT command using the WHERE clause in 1, will not find rows even if they do contain an asterisk. However, the CHANGE command will change those rows that contain the literal asterisk when using the WHERE clause in number 1. The second command will delete ALL the rows that have a value other than NULL. Be careful with this one. Using the where clause in number 2 with the select command results in only those rows containing the literal asterisk being listed but using it with the CHANGE command results in all non-NULL fields being changed. The bottom line: o It is easy to construct a NOT CONTAINS condition using the R:base 5000 wildcards. o It is a good idea not to use the asterisk (*) and/or question mark (?) in your data. o Be very careful when using wildcards in global data changing commands like DELETE, CHANGE, and ASSIGN. You may get unexpected results. ******************* AUTOMATIC NUMBERING ******************* With R:base 5000 it is very easy to automatically number certain columns such as CUST# with sequential numbers. Use variable forms to set up the data entry screen. This way you can have the customer number calculated and put into the VCUST# variable before even showing the form to the operator to enter the other information for that particular customer. In the command file you can calculate the number to start a given data entry session with the following two commands: COMPUTE VCUST# AS MAX CUST# FROM tblname SET VAR VCUST# TO VCUST# + 1 From then on all you need do is include the SET VAR VCUST# TO VCUST# + 1 command in your WHILE loop right before you DRAW the variable form for the next Note that I have put a V in front of the colname to indicate that it is a variable that will be loaded into that particular colname. Although this is not required; it is a big help later when you are trying to remember just what is a variable and what is a colname and which variables go into which columns. I even go so far as to try to make all my colnames 7 characters or less so that if I ever want to use a corresponding varname it is easy to simply insert a V as the first character. You may find this to be a useful "R:base 5000 programming/documentation standard" it will make maintenence of your application and training of new personnel much easier later on. ********************************* USING ALT 177 IN YOUR APPLICATION ********************************* Application developers have requested the ability to "shade in" the areas of the screen where operators are supposed to enter values so that they will know where to put the data. You can do this easily by using the ALT 177. To enter this character, hold the ALT key down and enter 177 on your number pad (do not use the numbers on top of the letters). The ALT 177 produces a shaded area on your screen the size of the cursor. It can be used to indicate the number of spaces to enter: o In response to a FILLIN command prompt o In a variable form o In a table form ALT 177s can be used as defaults in both kinds of forms to show the operators where to enter data. You should use it only when all the characters must be entered so that operators do not have to "blank over" the ALT 177s before going on to the next column or entry. To use them as defaults the easiest method is to create your form using the FORMS command, locate your variables or colnames, and then go back into the (E)DIT mode in FORMS and put ALT 177s in covering the S, the E, and all the spaces between them. ALT 177s work as text characters and can actually be entered into the database. However if your variable (in the case of variable forms) or your column (in the case of table forms) is defined as INTEGER, REAL, DOLLAR, TIME, or DATE, the entire area must be filled in or blanked over to remove all the ALT 177s or you will get an error message. This is a good way to ensure that a required field is entered when you do not want to have to use a RULE. To make sure that all character positions in a TEXT field are entered, you might think you could add a rule to trap an entry containing an ALT 177 in RULES by using the wildcards to create a "does not contain" condition. However, this does not work because RULES usually searches for the literal * and ? rather than using them as wildcards. The answer is to use variable forms for data entry. You are then able to make sure that all characters have been entered by using a section of "error trapping" code similar to the following (bear in mind that ALT 177s will not come over the modem - they look like "1"s): . . . WHILE varname EQ "*▒*" OR varname EQ "* *" THEN WRITE "ERROR - You must fillin the entire code" NEW DRAW varformname WITH ALL EDIT VAR varname USI varformname RETURN keylist ENDWHILE . . . This code will trap the operator in a loop until there are no longer any ALT 177s (or blanks) in the response. This particular code should not be used if you want embedded blanks because it does not allow it. If imbedded blanks were allowed the operator could simply blank over the ALT 177s and get out of the loop. ************ JUST FO FUN ************ Because customers are always asking us how their database could have been destroyed we put together the following song lyrics. Sing it to the tune of 50 WAYS TO LEAVE YOUR LOVER. We know that a destroyed database is no laughing matter, but we think that a little humor is a great tension reliever and maybe will even help in preventing future damage by encouraging you to keep several recent backups. So here it is ..... 50 WAYS TO TRASH YOUR DATA by John Pfeifer with chorus by Microrim Tech Support The problem is all inside your drives, they said to me The answer is easy if you buy new ones from me I'd like to help you, but there's the matter of my fee there must be 50 ways to trash your data Just treat it like a toy, Roy Spill your coffee or your tea, Lee Bump the keyboard on a pack, Jack Control C for a thrill, Jill Flip the main power switch, Mitch Swap your disks and retry, Guy Power surge on the line, Tyne? Reload the same name, Mame Copy only file two, Lou Static charge cause some pain, Jane? I think ya got the idea, Lia There must be 50 ways to trash your data He said it's really not my style to be so rude But I hope you realize that your setup is REAL crude Upgrade your processor and change that attitude There must be 50 ways to trash your data. Turn off the machine, Jean Format it all, Paul Error in your LOC.DAT, Matt An electrical storm, Norm Look at all that dust, Gus Backup bad over good, Wood Did you have a head crash, Nash? Wash your disks in the sink, Link Fry your data in the heat, Pete? Watch that parity err, Jere I think ya got the idea, Lia There must be 50 ways to trash your data We hope you don't get caught without a backup! ******************** HOW TO CALCULATE AGE ******************** Calculating the age of a person or item is easy with R:base 5000, simply issue the following three commands: SET V VBRTHDAY TO BRTHDAY IN tblname WHERE ... SET V VDAYS TO .#DATE - .VBRTHDAY SET V VAGE TO .VDAYS / 365.25 tech notes 5 ******************************************** NOTES #5 FROM TECH SUPPORT - AUGUST 16, 1985 ******************************************** New issues of these tech notes are published here every 2 to 3 weeks and much of the information will also be published in the R:base EXCHANGE. Back issues can be found in the FILES section. **************************************************** HOW TO MASK OUT THE .00 OF A DOLLAR FIELD IN REPORTS **************************************************** By Kay D. Dayss and MariEsther Burnham Using the following trick shot it is possible to mask out the cents portion (the .00) of a dollar field in the R:base report writer. This is especially useful on large dollar amounts when you don't care about the pennies. STEP 1. Go into REPORTS and call up your report. In (D)efine mode create a "blank" variable using the following expression: BLANK = " " Put 3 blanks inside the quote marks. STEP 2. Go into (L)ocate mode and locate the BLANK variable somewhere near the dollar column that you want to blank out the decimal point and cents on. STEP 3. Leave REPORTS saving your changes. Issue the following command: EDIT ALL FROM REPORTS WHERE RNAME = reptname Use the down arrow key to get to the LAYOUT section. You will know you are there when you see the word LAYOUT in the RDATA column. The next rows will have information similar to the following in the RDATA column for each of the located columns and variables: col/varname row# col# length ... First, find the row containing the colname for the DOLLAR column and note the row#. Next, subtract 3 from the length and add the result to the col#. Note the result. Now find the row containing the BLANK variable (it should be nearby). Change its row# to the one noted above and change its col# to be equal to the result of the calculation you did above. The BLANK variable will now print over the the last 3 places on the dollar column location. This procedure will only work if you locate the BLANK variable after you locate the DOLLAR column. ************************************************* LINING UP THE DECIMAL POINTS IN YOUR REAL NUMBERS ************************************************* By Kay D. Dayss It is possible, using the trick shot below, to line up the decimal points of REAL numbers in your reports. Keep in mind that without using scientific notation REAL numbers can use up to 7 total print positions (a maximum of 6 number positions plus the decimal point). Up to 5 numbers are possible to the right of the decimal point (when there is only 1 on the left) and up to 6 numbers to the left of the decimal point (when there are none to the right). When there are no numbers to the left of the decimal point a zero is printed and you are still only able to have 5 numbers to the right. Any numbers larger or smaller than the limits discussed above will cause the REAL number to be represented in scientific notation and this trick shot will not work on them. Therefore the trick to getting all the decimal points to line up is to use a TEXT variable of length 12. Basically, you will need to move the numbers into the TEXT variable in the right place putting the decimal point in position 7. Then using the change command place the TEXT variable into a special TEXT column you have set up in your table to hold the now alligned value. You would then print the TEXT column in your report. In this example the REAL column is REAL# and the TEXT column to be printed is PRINT# with a length of 12. SET VAR BLANK5 = " " *(5 blanks inside the quotes) SET VAR BLANK4 = " " *(4 blanks inside the quotes) SET VAR BLANK3 = " " *(3 blanks inside the quotes) SET VAR BLANK2 = " " *(2 blanks inside the quotes) SET VAR BLANK1 = " " *(1 blank inside the quotes) SET POINTER #3 VERR FOR tblname WHERE REAL# EXISTS WHILE VERR EQ 0 THEN SET VAR VREAL# TO REAL# IN #3 SET VAR VREAL# TEXT IF VREAL# CON ?.????? THEN SET VAR VREAL# TO .BLANK5 + .VREAL# GOTO DONE ENDIF IF VREAL# CON ??.???? THEN SET VAR VREAL# TO .BLANK4 + .VREAL# GOTO DONE ENDIF IF VREAL# CON ???.??? THEN SET VAR VREAL# TO .BLANK3 + .VREAL# GOTO DONE ENDIF IF VREAL# CON ????.?? THEN SET VAR VREAL# TO .BLANK2 + .VREAL# GOTO DONE ENDIF IF VREAL# CON ?????.? THEN SET VAR VREAL# TO .BLANK1 + .VREAL# ENDIF LABEL DONE CHANGE PRINT# TO .VREAL# IN #3 CLEAR VREAL# NEXT #3 VERR ENDWHILE ***************** XRW REPORT SAMPLE ***************** by MariEsther Burnham This is a sample of how to write an XRW program for the following situation: There is a Master file (PATIENTS) and a Transaction file (VISITS). Each patient may have made 0-n visits. The desired report is a listing of visits by patients, where patients who have not made any visits are not listed and the patient name is only printed once. The concepts presented in this example can be applied in any situation where you have a master record with 0-n transaction records associated with it. For example: o customers and their invoices o parts and their cost history o finished goods and their parts lists o invoices and their cash receipts This sample report also allows sorting by patient name. REPORT RVISIT FOR DRDB VARIABLES FLAG INTEGER END FOR EACH ROW IN PATIENTS SORTED BY LASTNAME FLAG = 0 {Initialize the flag variable} FOR EACH ROW IN VISITS SORTED BY TDATE WHERE PID = PID IN PATIENTS IF FLAG = 0 THEN {This is the first time through this loop} FLAG = 1 PRINT {print patient info} 2 10 "Patient name: " STRIP ( FRSTNAME ) " " STRIP ( LASTNAME ) END {print} END {if} PRINT {print visit info} 1 20 "Visit date: " TDATE 2 20 DESCRPT END {print} END {for each .. visits} END {for each .. patients} tech notes 6 ******************************************** NOTES #6 FROM TECH SUPPORT - AUGUST 30, 1985 ******************************************** New issues of these tech notes are published here every 2 to 3 weeks and much of the information will also be published in the R:base EXCHANGE. Back issues can be found in the FILES section. Note that a small change was made to the NOTES #5 editorial article. You may want to download the new version which can be found in the FILES section and is titled EDTORIAL.5. All that was done was to change all references to ALT 255s to actual blanks to prevent printer problems later on. ******************************** MOVING STRINGS TO NULL VARIABLES ******************************** by Mario Valverde PROBLEM: RE: R:base 5000 version 1.0 (fixed in version 1.01) When moving variable substrings to a variable which was previously null, if the value being moved is less than four characters in length, then, the res- ult of the move will produce a variable with a length of four. That is, the value being moved will be padded with trailing blanks. SOLUTION: Make sure that the receiving varaible is never null at the time it receives the character string. That is, make sure that the variable has been initial- ized to at least a single character value prior to receiving the new character string. EXAMPLE: SET VAR V1 TEXT SET VAR V2 TEXT SET VAR V1 TO "ZZZZZZZZZZZZZ" SET VAR V2 TO "Y" MOVE 2 FROM V1 TO V2 AT 1 The example above will produce a V1 variable value with a length of two. If the variable V2 had not been set to an initial value (i.e., was null), then, the resulting value of V2 would have been four characters in length. That is, it would have been ZZ__ where _ represents a trailing blank. ********************************** DEBUGGING YOUR EXPRESS APPLICATION ********************************** by Kay D. Dayss Application EXPRESS automatically turns messages, error messages, and echo off. This makes it hard to debug your application but you will want them off when demonstrating to your customer. An easy solution is to add a temporary menu pick on your main menu, choose CUSTOM as the action, and enter the following three commands: SET MESS ON SET ERR MESS ON SET ECHO ON Then when you want to debug your application all you have to do is first pick your debug menu choice before going to any of your other choices. You may also set up another temporary pick to turn debugging off. After the application is fully developed you can delete these picks from your main menu and your application is ready to be used. *********** QUICKY TIPS *********** by Kay D. Dayss PROBLEM: When unloading reports and/or forms into files, editing the files, and then trying to bring them back into the REPORTS and/or FORMS tables sometimes gives an error message indicating that excess data values will be ignored. The problem is that somewhere in your report or form, you have put quotes in (probably when you were in EDIT mode of FORMS or REPORTS). R:base then trys to make another field out of those quoted items. SOLUTION: Set QUOTES to an unused character such as # before doing the unload and then after loading the FORM or REPORT back in set QUOTES back to the " character. ============================================================ PROBLEM: A customer reported that a 60K file compiled into a 1000K file. SOLUTION: Before recompiling with RCOMPILE, erase the original compiled file so that a brand new compiled file is created. ============================================================ PROBLEM: Using the GT, GE, LT, LE operators in WHERE clause in combination with TEXT columns and the ASTERISK as a wildcard does NOT work. It gives you an error message: -WARNING- No rows satisfy the WHERE clause even though rows do satisfy it. SOLUTION: Use only the EQ or NE operators in combination with WHERE clauses and the ASTERISK. EXAMPLE: SEL ZIP FRO tblname WHE ZIP GE 11100 AND ZIP LE 55599 - will work SEL ZIP FRO tblname WHE ZIP GE 111* AND ZIP LE 555* - won't work ============================================================ PROBLEM: When printing a report everything bunches over to the left. SOLUTION: You may have ALT 255s in your TEXT columns - get them out by using the CHANGE command and everything will be AOK. The ALT 255 looks like a blank but when sent to some printers that support ascii codes only to 127 (such as the OKIDATA 2410 PACEMARK) it can cause some strange things to happen. Sometimes it sends a bunch of carriage returns and sometimes it prevents the printhead from advancing to the next position or tab stop. Remember to check any variables you are printing for the ALT 255 also. And remember that when you print a program you have written that has ALT 255s in it, you will find the same problem. Some printers, use ASCII codes up to and even beyond the ALT 255 and on these printers you will not experience any problems. EXAMPLE: CHANGE NAME TO -0- WHERE NAME CON "* *" *(NOTE: THERE IS AN ALT 255 INSIDE THE QUOTES AND BETWEEN THE ASTERISKS - TO ENTER IT HOLD THE ALT KEY DOWN, ENTER 255 ON THE NUMBER PAD, AND THEN RELEASE THE ALT KEY) ============================================================ ********************** ERROR CODE DEFINITIONS ********************** The following list contains the meanings of all the error code values that your error variables can take on: CODE MSG ---------- ------------------------------------------------------------------ 0 NO ERRORS 7 UNABLE TO OPEN DATABASE 8 MISSING DATABASE NAME 9 ILLEGAL DATABASE NAME 10 UNABLE TO CLOSE DATABASE 12 -WARNING- UNRECOGNIZED SET KEYWORD 13 ERRORS IN VARIABLES. CORRECT WITH REPORT COMMAND. 14 MORE THAN 1600 BYTES OF DATA FROM INPUT 15 ERROR IN WRITING 19 ERROR IN CLOSING 22 ILLEGAL BOOLEAN OPERATOR BETWEEN VARIABLE AND VALUE 26 SWITCHING INPUT BACK TO KEYBOARD 29 THE HELP TEXT FILE IS NOT AVAILABLE 30 THERE IS NO HELP TEXT FOR THE <COMMANDNAME> 31 UNABLE TO OPEN FILE 32 ILLEGAL FILE NAME 33 INPUT FILE NOT FOUND 39 SYNTAX IS INCORRECT FOR THE COMMAND 40 UNRECOGNIZED COMMAND - RETYPE IT 41 -WARNING- UNAUTHORIZED ACCESS TO THE TABLE 42 ILLEGAL TABLE NAME 43 <TBLNAME> IS AN UNDEFINED TABLE 44 I/O PROBLEMS - CHECK FOR FULL DISK 47 ILLEGAL COLUMN NAME 48 TOO MANY TABLES DEFINED - LIMIT IS 40 49 NEW TABLE IS WIDER THAN 1530 BYTES 50 TOO MANY COLUMNS IN THE DATABASE - LIMIT IS 400 51 MORE THAN 10 SORTED COLUMNS 52 NEW TABLE HAS TOO MANY COLUMNS 55 NEW TABLE NAME IS A DUPLICATE 56 -WARNING- NO ROWS SATISFY THE WHERE CLAUSE 57 RESERVED WORDS CANNOT BE USED FOR NAMES 62 -WARNING- AN OWNER PASSWORD SHOULD BE DEFINED WHEN USING PASSWORDS 63 -WARNING- THIS PASSWORD DOESN'T ALLOW YOU TO VIEW ANY TABLE 65 I/O ERROR ON OUTPUT FILE - CHECK FOR FULL DISK 67 INTERNAL ERROR IN LOC.DAT 70 ILLEGAL BOOLEAN OPERATOR BETWEEN COLUMN NAME AND VALUE 75 ILLEGAL LIMIT SPECIFICATION 82 THE PROMPT FILE IS NOT AVAILABLE 83 THERE IS NO PROMPT TEXT FOR THE COMMAND 85 YOUR ERROR VARIABLE MUST BE OF TYPE INTEGER 89 -WARNING- VALUE TRANCATED TO 40 CHARACTERS 89 -WARNING- VALUE TRUNCATED TO 40 CHARACTERS 90 TABLE NOT DEFINED 91 RULE COMPONENTS MUST APPLY TO THE SAME TABLE 92 UNRECOGNIZED BOOLEAN EXPRESSION 93 NO DATABASE OPEN 94 <COLNAME> IS AN UNDEFINED COLUMN NAME 95 COLUMNS MUST BE THE SAME TYPE AND LENGTH 96 VALUE INCOMPATIBLE WITH COLUMN 97 RULES MUST BE JOINED WITH AND or OR 98 TABLE MUST BE SPECIFIED 101 INSUFFICIENT SPACE TO PROCESS RULES 102 INCOMPATIBLE RULES TABLE ALREADY EXISTS 103 TOO MANY TABLES TO DEFINE RULES 104 TOO MANY COLUMNS TO DEFINE RULES 116 YOU MUST BE THE OWNER TO CHANGE RULE CHECKING 118 INCORRECT NUMBER OF VALUES FOR THIS TABLE 125 -WARNING- EXCESS DATA VALUES IGNORED 126 MISSING TABLE NAME 127 CANNOT LOAD WITH PROMPTS AND AN AS CLAUSE 128 CANNOT LOAD WITH PROMPTS AND FROM A FILE 129 NO FILE SPECIFICATION 131 INCORRECTLY SPECIFIED (ROW,COL) 132 UNIDENTIFIED AS CLAUSE 133 YOUR USER AND OWNER PASSWORD MUST MATCH 137 -WARNING- NO ROWS SATISFY THE WHERE CLAUSE 138 <RPTNAME> IS AN UNDEFINED REPORT 139 ALL VALUES IN QUALIFYING ROWS ARE MISSING 149 FUNCTION MUST BE COUNT, MIN, MAX, SUM, AVE, ROWS, OR ALL 150 SUM AND AVE ONLY WORK WITH INTEGER, REAL, AND DOLLAR COLUMNS 153 COLUMN IS TOO LONG FOR TALLY 154 TOO MANY VALUES FOR TALLY 166 -WARNING- NO TABLES ARE DEFINED 177 -WARNING- NO COLUMNS ARE DEFINED 181 COLUMNS MUST BE DEFINED BEFORE TABLES 185 ILLEGAL OWNER PASSWORD 186 YOU MUST ENTER THE CORRECT OWNER PASSWORD 193 EXISTING COLUMN NAMES CANNOT BE REDEFINED 194 ALLOWABLE COLUMN TYPES ARE TEXT, INTEGER, REAL, DATE, TIME, AND DOLLAR 196 COLUMN LENGTH MUST BE BETWEEN 1 AND 1500 197 YOU CANNOT SPECIFY A LENGTH FOR DATE, TIME, OR DOLLAR 198 ILLEGAL PASSWORD NAME 205 -WARNING- UNRECOGNIZED SET KEYWORD 206 ILLEGAL DATE FORMAT SPECIFICATION 207 SCREEN WIDTH MUST BE GREATER THAN 40 AND LESS THAN 256 209 VARIABLE LIMIT HAS BEEN REACHED 213 -WARNING- NO VARIABLES DEFINED 214 YOU MUST BE THE OWNER TO CHANGE RULES CHECKING 217 TOO MANY NESTER WHILE COMMANDS 218 TOO MANY COMMANDS IN A WHILE LOOP 219 RBEDIT CANNOT MAKE THIS FILE ANY BIGGER 220 DISK I/O ERROR IN PACK COMMAND 247 A WHERE CLAUSE IS REQUIRED 250 ASSIGN COMMAND REQUIRES INTEGER, REAL, OR DOLLAR COLUMN 251 CANNOT ASSIGN VECTOR COLUMN 252 EXPRESSION CANNOT BE EVALUATED 253 OPERATOR MUST BE + - X / OR % 267 OWNER PASSWORD IS INCORRECT 275 NEW NAME IS A DUPLICATE 276 ILLEGAL PASSWORD NAME 301 INCORRECTLY SPECIFIED (ROW,COL) 302 UNIDENTIFIED AS CLAUSE 303 YOUR USER AND OWNER PASSWORDS MUST MATCH 310 JOINING COLUMNS DIFFER IN TYPE OR LENGTH 316 UNABLE TO OPEN RELOAD FILE 317 MISSING DATABASE NAME 318 ILLEGAL DATABASE NAME 319 WRITE ERROR ON RELOAD FILE - CHECK FOR A FULL DISK 325 NO DATABASE OPEN 326 VARIABLES MUST BE 8 CHARACTERS OR LESS 336 SOME EXPRESSIONS CANNOT BE EVALUATED 344 INCOMPATIBLE REPORTS TABLE ALREADY EXISTS 345 TOO MANY TABLES TO DEFINE REPORTS 346 TOO MANY COLUMNS TO DEFINE REPORTS 349 -ERROR- NO COLUMN NAMES OR VARIABLES HAVE BEEN LOCATED 350 CANOT RECOGNIZE VARIABLE NAME 355 -ERROR- NOT A COLUMN OR VARIABLE NAME 356 ERROR(S) IN VARIABLES. CORRECT WITH DEFINE. 363 -ERROR- THERE ARE NO VARIABLES DEFINED 366 CANNOT RECOGNIZE TYPE 367 INVALID RESULT TYPE FOR EXPRESSION 368 CANNOT RECOGNIZE NUMBER 369 VARIABLE NOT DEFINED 370 NUMBER GREATER THAN THE NUMBER OF VARIABLES 371 NUMBER LESS THAN <N> 380 -ERROR- ALREADY 40 VARIABLES DEFINED 381 CANOT RECOGNIZE VARIABLE NAME 382 VARIABLE EXISTS. REDEFINE IT (Y/N)? 383 ILLEGAL CHARACTER IN VARIABLE NAME 384 VARIABLE NAME CANNOT BE A KEYWORD 385 VARIABLE CANNOT HAVE THE SAME AS A COLUMN 387 CANNOT RECOGNIZE EUAL SIGN (=) 388 CANNOT RECOGNIZE FIRST OPERAND 390 CANNOT RECOGNIZE OPERATOR 399 A WHERE CLAUSE IS REQUIRED 400 NO FORMS HAVE BEEN DEFINED 401 <FORMNAME> IS AN UNDEFINED SCREEN FORM 402 UNABLE TO OPEN FILE 403 ILLEGAL FILE NAME 404 INPUT FILE NOT FOUND 415 ROW OUT OF BOUNDS 417 COLUMN IS OUT OF BOUNDS 419 TOO MANY TABLES TO DEFINE FORMS 420 TOO MANY COLUMNS TO DEFINE FORMS 426 NO FORMS HAVE BEEN DEFINED 427 <FORMNAME> IS AN UNDEFINED SCREEN FORM 428 -WARNING- NO DATA EXISTS FOR THIS TABLE 429 INSUFFICIENT SPACE TO PERFORM EDIT 439 YOU HAVE MORE THAN 23 LINES ON THE FORM 440 FORM DEFINITION IS INCOMPLETE 441 LAYOUT DATA IS INCOMPLETE - CHECK IT OUT 442 YOU HAVE TOO MANY COLUMN NAME LAYOUTS 443 YOU HAVE DUPLICATE LAYOUT ENTRIES 444 YOU DO NOT HAVE ANY COLUMN NAMES DEFINED IN THIS FORM 446 REPORT DEFINITION IS INCOMPLETE 447 REPORT DATA IS INCORRECT - CHECK IT OUT 453 EXPRESSION CANNOT BE EVALUATED 455 SPECIFIED OFFSET OUT OF RANGE 462 ENDWHILE OR ENDIF MISSING IN AN INPUT FILE 464 ATTEMPTING TO IMPROPERLY TERMINATE A WHILE OR IF BLOCK 465 WHILE AND IF COMMANDS ARE NESTED BEYOND MAXIMUM 466 NO WHILE OR IF BLOCKS ARE OPEN ON CURRENT INPUT SOURCE 468 THE SCHEMA OPTION MAY NOT BE USED WITH AS DIF OR AS MPLAN 474 YOU DON'T HAVE PERMISSION TO MODIFY REPORTS 480 YOUR VALUE DOES NOT HAVE THE SAME TYPE AS YOUR VARIABLE 484 VARIABLE TYPE DOES NOT MATCH COLUMN TYPE 485 NO COLOR BY THAT NAME 486 YOU CAN'T HAVE THE SAME FOREGROUND AND BACKGROUND 487 THAT COLOR CAN'T BE USED AS BACKGROUND 488 VARIABLE NOT DEFINED 490 VARIABLE TABLE IS FULL, USE AN EXISTING VARIABLE OR CLEAR A VARIABLE 491 NUMBER OF CHARACTERS MUST BE 1 TO 1500 492 VARIABLE MUST BE AN INTEGER 493 INVALID ROUTE NUMBER OR ROUTE DOES NOT EXIST 494 YOU DON'T HAVE MODIFY PERMISSION ON FORMS 495 INVALID OPERATOR 496 CANNOT RECOGNIZE SECOND OPERAND 498 ERROR(S) IN VARIABLES. CORRECT WITH DEFINE. 500 SORTING FILE PROBLEMS - CHECK FOR FULL DISK 504 INSUFFICIENT SPACE TO PROCESS REPORTS COMMAND 505 -ERROR- MAXIMUM NUMBER OF COLUMN NAMES, VARIABLES LOCATED 509 VARIABLE LIMIT EXCEEDED 510 -ERROR- PAGESIZE MUST BE LESS THAN 1000 512 FILE TOO LARGE FOR RBEDIT 526 FILE NOT FOUND 527 INVALID DIRECTORY 528 INVALID DRIVE 538 COLUMN LENGTH MUST BE BETWEEN 1 AND 1500 539 YOU CANNOT SPECIFY A LENGTH FOR DATE, TIME, OR DOLLAR 540 COLUMN MAKES TABLE WIDER THAN 1530 BYTES 545 <COLNAME> IS IN ANOTHER TABLE. USE A DIFFERENT NAME 548 TOO MANY COLUMN NAMES IN THE DATABASE - LIMIT IS 400 550 UNABLE TO RENAME FILE 551 OUT OF FILE HANDLES 561 ROW IS OUT OF BOUNDS 562 COL IS OUT OF BOUNDS 563 VARIABLE IS NOT FOUND 564 VARIABLE MUST BE AN INTEGER 565 NUMBER IS OUT OF RANGE 566 DISPLAY OR RUN PROCEDURE NOT FOUND 567 NOT A RUN PROCEDURE 568 NOT A DISPLAY PROCEDURE 569 CAN'T OPEN SOURCE FILE 570 CAN'T CREATE DESTINATION FILE 571 DISK FULL 576 INVALID DRIVE 589 INTERNAL ERROR - OUT OF DYNAMIC SPACE 590 INTERNAL ERROR - HEAP ALLOCATION ERROR 592 NOT A MENU PROCEDURE 593 INVALID DATA IN THE MENU FILE 594 UNABLE TO FIND MATCHING LABEL FOR <LABELNAME> 595 INTERNAL ERROR - OUT OF DYNAMIC SPACE 601 TOO MANY VARIABLES DEFINED. PRESS ANY KEY TO CONTINUE 604 CANNOT DRAW ANY PART OF A FORM ABOVE OR BELOW SCREEN LIMIT 605 INTERNAL ERROR - OUT OF DYNAMIC SPACE 605 NO FORMS HAVE BEEN DEFINED 607 <FORMNAME> IS AN UNDEFINED SCREEN FORM 609 INTERNAL ERROR REFERENCING VARIABLE 612 <TBLNAME> IS NOT A VALID TABLE 613 <COLNAME> IS NOT A COLUMN IN TABLE <TBLNAME> 614 <OPERATOR> IS NOT A VALID OPERATOR 622 CONNOT RECOGNIZE WHERE OPERATOR 623 WHERE CLAUSE IS INCOMPLETE 624 TOO MANY CONDITIONS IN THE WHERE CLAUSE 635 VARIABLE TYPE DOES NOT MATCH COLUMN TYPE 637 ERROR VARIABLE NOT FOUND 639 INVALID SHOW PARAMETER 2020 VARIABLE <VARNAME> IS NOT DEFINED 2045 -ERROR- COLUMN <COLNAME> IS NOT IN THE TABLE <TBLNAME> 2053 -ERROR- COLUMN NAME <COLNAME> IS USED MORE THAN ONCE 2058 -WARNING- LENGTH AFTER INTEGER OR REAL DEFINES A VECTOR. PLEASE ENTER NO LENGTH 2071 -ERROR- COLUMN <COLNAME> MUST BE COMPARED TO <DATATYPE> VALUE 2076 <COLNAME1> AND <COLNAME2> DATA TYPES CANNOT BE COMPARED 2099 TOO MANY CONDITIONS FOR A RULES. THE MAXIMUM IS 10. 2121 COLUMN <COLNAME> MUST BE A VALID <DATATYPE> 2123 -WARNING- COLUMN <COLNAME> WILL BE TRUNCATED 2136 -WARNING- NO DATA EXISTS FOR THIS TABLE 2137 -WARNING- NO ROWS SATISFY WHERE CLAUSE - END-OF-DATA ENCOUNTERED (SET POINTER) 2182 VALID DEFINE COMMANDS ARE OWNER, COLUMNS, TABLES, PASSWORDS, LIST, RULES, ETC. 2189 COLUMN <COLNAME> IS ALREADY USED IN THIS TABLE 2194 ALLOWABLE COLUMN TYPES ARE: TEXT, INTEGER, REAL, DATE, TIME, AND DOLLAR 2199 THE FOLLOWING COLUMNS ARE NOT ASSOCIATED WITH A TABLE 2201 THE ABOVE COLUMN NAMES WILL BE LOST IF YOU LEAVE THE DEFINE MODE 2273 NEW NAME IS ALREADY BEING USED FOR A COLUMN WHICH DIFFERS IN TYPE OR LENGTH 2277 COLUMN <COLNAME> IS NOT COMMON TO EITHER TABLE 2279 -ERROR- TABLES <TBLNAME1> AND <TBLNAME2> HAVE NO COMMON COLUMN NAMES 2307 -WARNING- <COLNAME> IS A DUPLICATE COLUMN NAME, YOU SHOULD RENAME ONE OF THEM 2529 TYPE CONVERSION ERROR CHANGING <VARNAME> TO TYPE <DATATYPE> 2531 CANNOT EXECUTE <COMMAND> WITH MORE THAN 39 TABLES DEFINED 2534 -ERROR- COLUMN <COLNAME> IS NOT DEFINED 2536 ALLOWABLE COLUMN TYPES ARE TEXT, INTEGER, REAL, DATE, TIME, AND DOLLAR 2541 -ERROR- COLUMN <COLNAME> IS ALREADY USED WITH THIS TABLE 2546 CANNOT USE THE REMOVE COMMAND WITH ONLY ONE COLUMN IN THE TABLE 2610 VARIABLE <VARNAME> NOT DEFINED IN THE FORM *************************** DATA TYPE IN VARIABLE FORMS *************************** by Marianne Jeager When you define a form, it is stored in the Forms table. If you do a "select all fro forms" and look in the layout section, you normally see 3 numbers after the column name. These numbers represent the line number, beginning position number and the located length. With variable forms, there is a fourth number after the variable name, with a code indicating the data type of the variable. The codes are as follows: 1 integer 2 real 4 date 5 time 6 dollar -20 text (the negative of the length of the text) tech notes 7 *********************************************** NOTES #7 FROM TECH SUPPORT - SEPTEMBER 20, 1985 *********************************************** New issues of these tech notes are published here every 2 to 3 weeks and much of the information will also be published in the R:base EXCHANGE. Back issues can be found in the FILES section. ********************************* LONG VARIABLE LENGTH TEXT COLUMNS ********************************* Some applications use long text fields and customers have indicated that they would like to be able to store these fields as variable length "memos" as some other products allow. The following files can be used to mimic this "memo" feature. You may of course adapt the example presented by these files to your own needs. If your application is TEXT intensive, you will want to pay particular attention to this article. RUN BUILD.CMD ------------- This command file will create a database with one table consisting of 2 columns. RUN MEMO.CMD ------------ This command file will present you with a menu of 3 choices: 1 Create a memo 2 Print a memo 3 Quit In this example a memo number is assigned to each memo entered. This number is the only thing that is actually stored in the database. Also, the number is turned into a DOS filename by adding .MEM as an extension. Therefore, if 55 is the memo number, a file called 55.MEM is created. The actual text inside the memo is stored in the 55.MEM DOS file. Depending on your needs, a number or a name may be stored in the R:base TEXT column that holds the memo name and used as the actual DOS filename. R:base stores the filename and DOS holds the file. This feature could be extended and used to store a long variable length text column for every row in your table. You would only be limited by the maximum number of files allowed by DOS. You could set up your application to use the number in your unique record id column (such as CUST#, PROJ#, INVOICE#, SSN# or whatever) as the memo identifier and DOS file name. You could use a form to enter all the information except the long TEXT column and use macros similar to those below to enter the long TEXT column. Since DOS files are used, each memo name would have to be unique and conform to DOS file naming conventions. The contents of the MEMO is not stored in the database, only its name is stored. The file folding the contents is variable length depending on the data stored in that file. If you were using this feature to store a variable length TEXT item for ea h row in your table, you would have a DOS file on your disk for every row in your table. Clearly, if your DOS systems only allows you to have 64 files, this would not be a viable option unless you have only a handfull of rows. You will need to check with your hardware manufacturer to find out how many files you can have on your system. It could be different depending on what machine you are on. For example, if you have an IBM machine with an IBM hard disk, are using PC-DOS, and have your files in subdirectories, you may have as many files as you want. Also keep in mind that if you have 10,000 rows you could have 10,000 files; so doing directories with the DIR command could take a long time. RBEDIT is used to create each file. So the size limit on each "memo" is large. You can have 769 lines times 80 characters per line for a maximum of 61,520 characters. In other words, R:MEMO feature (described here) allows memos 15 times larger than those that d other memo feature allows. With this R:MEMO feature, you could have a memo well over 10 normal pages long for one row in your table and one only a line or two long for another row and not waste disk space. Contents of the BUILD.CMD: -------------------------- DEFINE MEMO COLUMN NAME TEXT 8 MEMOFILE INTEGER TABLE MEMO WITH NAME MEMOFILE END Contents of the MEMO.CMD file: ------------------------------ SET ECHO OFF SET MESSAGES OFF SET ERROR MESSAGES OFF SET BELL OFF OPEN MEMO SET LINES 24 SET VAR LINE INT; SET VAR LINE TO 15 *(ADJUST THESE VALUES TO FIT YOUR MENU SCREEN) SET VAR COL INT; SET VAR COL TO 35 NEWPAGE DISPLAY MEMO.TXT WRITE "PRESS ANY KEY TO CONTINUE" PAUSE NEWPAGE DISPLAY MEMO.MNU FILLIN ANS USING "ENTER YOUR CHOICE " AT .LINE .COL IF ANS = 1 THEN WRITE " " AT .LINE .COL FILLIN NAME USING "ENTER NAME " AT .LINE .COL WRITE " " AT .LINE .COL FILLIN MEMO USING "ENTER MEMO NUMBER " AT .LINE .COL LOAD MEMO .NAME .MEMO END SET VAR FILE TO .MEMO + ".MEM" RBEDIT .FILE ENDIF IF ANS = 2 THEN WRITE " " AT .LINE .COL FILLIN NAME USING "ENTER NAME " AT .LINE .COL SET VAR MEMOFILE TO MEMOFILE IN MEMO WHERE NAME = .NAME SET VAR FILE TO .MEMOFILE + ".MEM" NEWPAGE DISPLAY .FILE WRITE "---------------------------------" WRITE "PRESS ANY KEY TO CONTINUE" PAUSE ENDIF IF ANS = 3 THEN NEWPAGE SET LINES 20 SET MESSAGES ON SET ERROR MESSAGES ON SET BELL ON RETURN ENDIF CLEAR ALL VAR QUIT TO MEMO.CMD Contents of the MEMO.MNU file: ------------------------------ [[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[ [[ [[ [[ 1 CREATE A MEMO [[ [[ [[ [[ 2 PRINT A MEMO [[ [[ [[ [[ 3 QUIT [[ [[ [[ [[ [[ [[ [[ [[ [[ [[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[ Contents of the MEMO.TXT file: ------------------------------ ████████████████████████████████████████████████████ ████ ████ ███ R : M E M O ███ ████ ████ ████████████████████████████████████████████████████ *************************************** R:BASE 5000 VERSION 1.01 - SYSTEM DISKS *************************************** If you are using a floppy disk system, note that R:base 5000 version 1.01 has a different loading procedure than version 1.0. 1.01 system disk I contains the EXE file 1.01 system disk II contains the OVL file THIS IS THE REVERSE OF VERSION 1.0! Although it will be familiar to users of R:base 4000. There is also a different floppy disk loading procedure. When following these instructions (pages 1-8 to 1-10 of the 1.01 manual) you MUST have a disk in drive B or you will get the abort, retry, ignore message. If you get this message press A to abort and start over with a disk in drive B. Floppy disk users can now use the RBASE.DAT file by putting it on their database disk and having their database disk in drive B when they start R:base. ************************************ SOLUTION FOR GRAND TOTALS IN REPORTS ************************************ Briefly, the problem was that with both subtotals and grand totals in a report, the grand total added in the last item twice in version 1.0 of R:base 5000 (not a problem in 1.01). A user reported that he made his date field a break point, and reset his grand total at this break. He says it works great. ******************************* HOT TIPS FROM TECHNICAL SUPPORT ******************************* PRODUCT: 5K VERSION: 1.0 SOLUTION: When the manual tells you (on page 17-14) to start looking at the sample application disk files by typing INIT.ORD, men0proc.ord, men1proc.ord, etc. note that these are actually blocks in the disk files PROCS2.ASC, MENUS.ASC. etc. In other words, they do not exist as stand-alone files. You will find INIT as a block in the PROCS2.ASC file right after a $COMMAND line that indicates that it is a command block. You will see that the INIT block passes control passes control to the main menu processing file, MENUS.ORD which has 6 $MENU blocks named MEN1PROC, MEN2PROC, etc. Note too that files with extensions of .ASC and .APP are usually ascii files and can be read. The are also known as APPLICATION FILES and are not compiled files. On the other hand, files with .ORD and .APX are generally compiled PROCEDURE FILES and cannot be read. ============================================================ PRODUCT: R5K VERSION: 1.0 PROBLEM: Load command against unopened database produces "table does not exist error". SOLUTION: Always open your database before issuing R:base commands that relate to that database. ============================================================= PRODUCT: R5K VERSION: 1.0 PROBLEM: Typo on p. 10-3, syntax example "2from" SOLUTION: "2 from". ========================================================= PRODUCT: 5K VERSION: 1.0 PROBLEM: Edit or enter table form after calling a variable form and get error message "form definition is incomplete." SOLUTION: Put the command "new" immediately before calling the table form. ============================================================ PRODUCT: 5K VERSION: 1.0 PROBLEM: In 4K users could set a variable to a blank (alt 255) then they could show this variable to blank out a line on the screen. This does not work in 5K only one char. is blanked out. SOLUTION: WRITE " " + AT row# col# ============================================================ PRODUCT: 5K VERSION: 1.0 PROBLEM: SET POINTER and keyed columns SOLUTION: Do not use a keyed column in the WHERE clause with the EQ or = operator in a SET POINTER command line. Use contains, GT or LT and all rows will be found or delete the key. This problem has been fixed in version 1.01 ============================================================================= PRODUCT: EXPRESS VERSION: 1.0 PROBLEM: ERROR too many menus defined. SOLUTION: Express limits are 15 menus total in 3 levels. ============================================================================= PRODUCT: VERSION: PROBLEM: User builds an application with EXPRESS and says YES to an RBASE.DAT file. User then wants to get to R> from his/her application but they always exit to DOS or family driver menu. The RBASE.DAT file looks like this: RUN APPL IN APPL.APX EXIT SOLUTION: They can hit (ESC) multiple times from the main application menu to get to the R> or delete/rename the RBBASE.DAT file while at the DOS prompt. The EXIT action option in Express takes you to previous menu or the R>. ============================================================================= PRODUCT: EXPRESS VERSION: ALL VERSIONS PROBLEM: Express wrote application files correctly. When ran got: -ERROR- pick2 is undefined variable SOLUTION: She was using macro w/CLEAR ALL VARS in it. Instead, she should clear only specific variables. ============================================================================= PRODUCT: R5K VERSION: 1.0 PROBLEM: User wants to select all records for a specific month or day or year. SOLUTION: Set date format so that the desired part of date is first, (e.g., mm/dd/yy to select month, dd/mm/yy to select day, and yy/mm/dd to select year). Then, to find all the 1985 records, for example, you would issue the following command: SEL collist FROM tblname WHE datecol = 85/*/* You can set date format to mmddyy and use MAY/*/* or yymmdd and use 1985. ============================================================================= PRODUCT: R5K VERSION: all versions PROBLEM: Loading a fixed field ascii file using a form, and some of the other fields have "missing values" that are to be loaded as nulls. The fields in question are type integer. SOLUTION: Wrong Solution: You cannot put a -0- in the field, you cannot put a '*', for example, and then set null *. In both instances you get the error message, field is not a valid integer. Right Solution: Fields that are to contain "missing values" must be left blank. They are then loaded as null. ============================================================================== PRODUCT: R5K VERSION: all versions PROBLEM: User cannot get SET COLOR command to work from R> SOLUTION: Type in MODE CO80 at the DOS prompt before entering rbase. =============================================================================== PRODUCT: R5K VERSION: 1.0 PROBLEM: Enter Print rptname, output to terminal, all that comes back is: more output follows - press [ESC] to quit, any key to continue User presses any key, the screen clears, and the same message is written on top line. It keeps going like this forever. SOLUTION: User has not MARKED anything on the report or has not MARKED any detail lines or set up any breaks. User needs to go back into reports and MARK. ============================================================================= PRODUCT: XRW VERSION: 1.2 PROBLEM: If the error message "Unable to open control directive file" appears when attempting to use XRW, the operator has probably issued the command XRW -R (in an attempt to suppress SOLUTION: XRW will accept a valid file name after the XRW command. E.G., XRW HOT.CMD Where HOT.CMD contains commands such as: RUN MYREP.REP PRN ============================================================================= PRODUCT: 5K VERSION: 1.0 SOLUTION: Rule Processing - Rules are checked in the order the rules appear in the rules table, not in the order the data is entered. If there are two mistakes on a table form, and neither passes rules, the first message displayed will be that of the first rule, rather than the first piece of data entered. Try this with a load with prompts, and you'll see how it works. ============================================================================= PRODUCT: R5K VERSION: 1.0 PROBLEM: Edit or enter table form after calling a variable form and get error message "form definition is incomplete." SOLUTION: Put the command "new" immediately before calling the table form. ============================================================================= PRODUCT: R5K VERSION: 1.0 PROBLEM: In 4K users could set a variable to a blank (alt 255) then they could show this variable to blank out a line on the screen. This does not work in 5K - only one character is blanked out. SOLUTION: Use the write command to write a blank line to the screen, eg write " " AT 23,1 ============================================================================= PRODUCT: R5K VERSION: all versions PROBLEM: In reports define V1 = anything and then define V2 = V1 IN tblname WHERE. . . V2 = .V1 IN tblname WHERE. . . neither of these two expressions are allowed in REPORTS. ============================================================================= PRODUCT: R5K FileGateway VERSION: any version PROBLEM: 5K Gateway - Couldn't run it or would get to main menu and couldn't exit. Still need SET NO87 on AT. OK with Enhanced Graphics Adapter. ============================================================================= PRODUCT: 5K VERSION: any version PROBLEM: Initializing the #RETURN variable before using it in a variable form can cause strange results such as data being scrambled. SOLUTION: 1) Have them replace #RETURN throughout their command file with another varname (e.g., XXX). 2) Have them put a line immediately after each EDIT VAR or ENTER VAR that says: SET VAR XXX to #RETURN If both these steps are done, the problem should go away. ============================================================================= PRODUCT: 5K VERSION: 1.0 PROBLEM: Grand total in report is not correct, it looks like the last item is added twice. SOLUTION: Check to see if the user is printing a subtotal in a PAGE footing and reseting that variable at end of page. This causes grand total of that column printed in REPORT footing to be off. (e.g., SUBTOT = sum of column/printed and reset in page footer. GTOT = sum of column/printed in report footer). If subtotal is printed/reset as break footer, it does total correctly. ============================================================================= PRODUCT: 5K VERSION: 1.0 PROBLEM: Loading a table with dotted variables when one or more of the variables contain null value can cause a problem when null is set to blank with: SET NULL " " SOLUTION: Best workaround - SET NULL -0- LOAD tablum .V1 .V2 .V3 END SET NULL " " If you surround the LOAD block with the SET NULL command like this then when you are drawing your variable form your user won't see the -0-, and the load will work correctly. ============================================================================= PRODUCT: R5K VERSION: 1.0 PROBLEM: When converting from 4K to 5K the date fields got turned into a text field. This was because in 4K the date was set to yy/mm/dd. SOLUTION: If you set the date in 5K to match your 4K format before doing the convert then everything is AOK. ============================================================================= PRODUCT: R5K VERSION: 1.0 PROBLEM: When defining a password for a database, to test the password, user must exit Rbase and reboot Rbase before the password will work properly. ============================================================================= PRODUCT: R5K VERSION: 1.0 PROBLEM: In reports when you have a page total and a report total totalling the same column, the report total will add the last record twice pagetot = pagetot + column(x) reptot = reptot + column(x). SOLUTION: To fix this, change the definition to look like this Pagetot = pagetot + column(x) junk = column(x) reptot = reptot + junk. ============================================================================= PRODUCT: R5K VERSION: 1.0 PROBLEM: Rbase takes 237,088 bytes of memory after setting buffers/files/path. The family driver needs approximately 30,352 bytes memory. Rbase will also use up to an additional 64K if available. Therefore the maximum bytes free Rbase 5000 will use is approximately 332,976. This figure is based on Version 1.0 and may change with future versions. ============================================================================= PRODUCT: R5K VERSION: 1.0 PROBLEM: User had XT with Orchid Turbo 186 card. When tried to open DB in turbo mode, got "Out of file handles". OK when ran without turbo. Apparently the turbo mode uses it's own config.sys file. He had only set the config.sys for regular mode. ============================================================================= PRODUCT: R5K VERSION: 1.0 SOLUTION: Delete duplicates uses keys if there are any. ============================================================================= PRODUCT: R5K VERSION: 1.0 PROBLEM: Customer has device driver for external HD. CONFIG.SYS file has 1)device driver for HD. 2) set buffers. 3) files. Wouldn't work. SOLUTION: Moving it so order was: buffers & file settings, device driver for HD. Then was OK. ============================================================================= PRODUCT: R5K VERSION: 1.0 PROBLEM: Foreign versions, Sorts & special characters, Printers. Special ascii characters have ascii character values greater than the standard letter values. Since our sorts are based on ascii values, these special characters do not sort correctly (alphabetically). Also, American printers handle these different than printers from the countries for whom these characters are normal. To print a, backspace, print an apostrophe. This will not work well on all printers. ============================================================================= PRODUCT: R5K VERSION: 1.0 PROBLEM: If you set a variable to be used as menu variable in choose command to any type including integer then choose command produces error message "internal error referencing variables". SOLUTION: Don't initialize the variable used by the CHOOSE command. ============================================================================= PRODUCT: R5K VERSION: 1.0 PROBLEM: Customers are unloading reports and then attempting to bring them back in with the input command. They are getting an error message telling them that excess data values will be ingored. The problem is that they have put quotes around values in their variable definition section. SOLUTION: Set quotes to an unused character like the # key before doing the unload and then after the input set it back to ". ============================================================================= PRODUCT: R5K VERSION: 1.0 PROBLEM: When moving variable substrings to a variable which was previously null, if the value being moved is less than four characters in length, then, the result of the move will produce a variable with a length of four. That is, the value being moved will be padded with trailing blanks. SOLUTION: Make sure that the receiving varaible is never null at the time it receives the character string. That is, make sure that the variable has been initialised to at least a single character value prior to receiving the new character string. Example: SET VAR V1 TEXT SET VAR V2 TEXT SET VAR V1 TO "ZZZZZZZZZZZZZ" SET VAR V2 TO "Y" MOVE 2 FROM V1 TO V2 AT 1. The example above will produce a V1 variable value with a length of two. If the variable V2 had not been set to an initial value (i.e., was null), then, the resulting value of V2 would have been four characters in length. That is, it would have been ZZ where 00 (2 blanks) represents a trailing blank. ============================================================================= PRODUCT: R5K VERSION: 1.0 PROBLEM: DBIII wants text nulls as " ". SOLUTION: To get this do the following: SET QUOTES=';SET NULL '" "';SET QUOTES=" Then R5K will unload nulls in a way DBIII can read. ============================================================================= PRODUCT: R5K and R4K VERSION: 1.0 and 1.01 PROBLEM: Why different results with same command file under 5K 1.0 & 4K 1.15? 5K appear to round. 4K 1.15 appears to truncate. SOLUTION: Some commands didn't round correctly in R:base 5000 version 1.0 (particularly the ASSIGN command) all round correctly in 1.01. All calculations should round in Ver 1.01 R5K. In version 1.15 of R:base 400 calculations truncated. ============================================================================= PRODUCT: 4K and 5K VERSION: all versions PROBLEM: This is a way to # pages in reports in 4000. Calculate how many rows will be printed on a page, Example: 2 header lines, 5 detail lines, pagesize = 52. #rows/page =(52 - 2)/5 = 10) 2. Define expressions as follows: rowct = rowct + 1 vp = rowct/10 (this is result of step 1 vp2 in page header section. 3. Locate up2 in page header section. SOLUTION: This will also be useful for 5K people who want to number pages in reports by company. Often they want to start the numbering sequence over when company changes, and the #PAGE system variable cannot be reset. ========================================================================== PRODUCT: R5K VERSION: 1.01 PROBLEM: Run command1; endif on a line will not run command1. Input command2; show var; pause will show var and pause before command2 will be run, but it will run. SOLUTION: The RUN and INPUT commands are interpreted by the computer as : set a flag so that the next time input is needed for this command line (the line the input or run statement is on) run or input the command or file. However, if a semicolon follows the INPUT or RUN, the commands following the semicolon are run because they are on that command line. If what follows the semicolon does not allow input for the current line (like ENDIF or another RUN command) then the first command will not be executed. For example: RUN F1;RUN F2;RUN F3 *(will only run F3) INPUT F1.DAT; LOAD tblname *(will load tblname with contents of F1.dat) RUN F1; SHOW VAR; EXI *(will do the SHOW VAR and then EXIT R:base; F1 will never be run) RUN F1; SET VAR V1 INT *(will set V1 to Integer and then will run F1) ============================================================================= PRODUCT: R5K VERSION: 1.0 SOLUTION: When editing data with variable forms, rather than a series of change commands, it may be faster to: DEL ROW FRO tblname WHE keycol = .Vkeycol LOAD tblname .V1 .V2 . . . END For this method to work you must have put all of the column values for the row into the variables. *************** BOOKS ON R:BASE *************** This is a list of books on R:base that are currently available or that will be available by the spring of 1986. Contact your local bookstore to order these titles. Microrim currently offers "Managing Information with Microcomputers" through our telemarketing service. Call 1-800-547-4000 to order this book. BOOK NAME: Using R:base 5000 AUTHOR: Jonathan Erickson / Nicolas Baron PUBLISHER: Osborne McGraw Hill 2600 Tenth St. Berkeley, CA 94710 CONTENT: Follow up to OMH Using R:base 4000. Provides an overview of R:Base 5000. Covers design issues, building forms and reports, a brief section on the programming language and a review of CLOUT and XRW use with R:base. DUE DATE: October 1985 BOOK NAME: Understanding R:base 5000 AUTHOR: Allan Simpson PUBLISHER: Sybex 2344 6th St. Berkeley, CA 94710 CONTENT: Written for new users of R:base 5000. Includes glossary of terms, explanation of commands, how to build your own programs and applications, interface with other software and how to convert R:Base 4000 files to R:base 5000. DUE DATE: Currently available. September 1985 BOOK NAME: Database Processing With Microcomputer Applications AUTHOR: David Kroenke / Don Nilson PUBLISHER: SRA College Division 10th Fl. 155 N. Wacker Dr. Chicago, IL 60606 ATTN: Michael Carrigg (312) 984-7098 CONTENT: College textbook format. Several complete examples are illustrated to study development and use of database applications. Detailed sections on Database Design and Database Applications are provided along with sections on programming with R:base 5000. DUE DATE: After January 1, 1986. NOTES: SRA will be provide textbook with 4 disk to universities and corporations for classroom use. Microrim to sell book only, not book bundled with disks, through our telemarketing services and through retail bookstores. BOOK NAME: R:BASE 5000 FOR THE PROGRAMMER (A How-To-Do-It Book) AUTHOR: Nelson T. Dinerstein PUBLISHER: Scott, Foresman and Company 1900 E. Lake Ave. Glenview, IL 60025 CONTENT: A programmers guide to building applications with R:base 5000. Explores use of the programming language, programming concepts, advanced design principles and an example of an R:base 5000 application. DUE DATE: March 1986 R:BASE 4000 BOOKS BOOK NAME: Managing Information with Microcomputers AUTHOR: David Kroenke / Don Nilson PUBLISHER: Microrim, Inc. 3380 146th Place Bellevue, WA 98007 CONTENT: Good overview of R:base 4000. Simple examples illustrate database design, menu building, createing forms and reports. Examples of use in marketing, manufacturing, finance and project management are provided. Use of CLOUT natural language query software is discribed. DUE DATE: Currently available. 1984 release. BOOK NAME: Using R:base 4000 AUTHOR: Jonathan Erickson / Nicolas Baron PUBLISHER: Osborne McGraw Hill 2600 Tenth St. Berkeley, CA 94710 CONTENT: General information book about R:base 4000. Covers design issues, reports, relational commands, and use of CLOUT along with helpful hints section. DUE DATE: Currently available. 1985 release. BOOK NAME: The Book of R:Base AUTHOR: Gilbert M. Roeder PUBLISHER: Retreival Technology Corp. All-Hands-On Press 3 Courthouse Lane Chelmsford, MA 01824 CONTENT: Good explanation of R:base 4000 functions. Simple straightforward approach. Also covers Microrim's Extended Report Writer and CLOUT. DUE DATE: Currently available. 1984 tech notes 8 ****************************************** TECHNICAL EDTORIAL #8 -- December 25, 1985 ****************************************** Copyright (c) 1985 by Microrim, Inc. *************** APPLICATION TIP *************** Many R:base 5000 programmers have requested the ability to execute a complete command line that has been stored in a variable. This is particularly useful when you want the user to enter an R:base command line in the middle of your application file. It allows your application to be more flexible. You can do this by storing the command line in a dummy command file and then running the command file. For example, the following segment of code will run whatever command line the user enters at the MY-APP> prompt: . . . WRITE "Enter the command + line you wish to execute + at the MY-APP> prompt" FILLIN VLINE USING "MY-APP>" OUTPUT DUMMY.CMD SHOW VAR VLINE=70 OUTPUT SCREEN RUN DUMMY.CMD . . . Use this macro in your application when you want to pass control to the operator. *************** MULTI-UP LABELS *************** Product: R:base 5000 all versions R:base 4000 1.1 or higher Before reading this article you may want to review chapters 11 and 15 of the R:base 5000 Manual. Also note that some of the methods discussed in this article require some knowledge of programming concepts. There are several methods in current use that will print "multiple across" (two-up, three-up, or four-up) labels. Each of them has its own unique advantages and disadvantages. This article is about four of these methods. Pick the one that is best for your particular needs. Each one of these methods will also help you to learn how to write R:base 5000 programs. Even if you are not printing labels you may want to examine these methods for the examples of R:base programming that they provide. The four methods (SIMPLE LOOKUP, CONTROL TABLE, LABLES.MAC, and PRINT TABLE) are described below. All but the last method can only be done with R:base 5000. The last method works in either R:base 4000 (version 1.1 or higher) or R:base 5000. The first two of the methods (SIMPLE LOOKUP AND CONTROL TABLE) are more completely detailed in other articles in this BBS EDITORIAL. 1. The SIMPLE LOOKUP Method ---------------------------- This method is detailed with step-by-step instructions in the article, "Multi-Up Labels: Simple Lookup Method" in this BBS EDITORIAL. Basically, it uses simple programming and the report writer. This method does not require that you create a temporary table. Instead it does lookups on its own base table using the report writer. Because no projecting or packing is necessary, it may be faster than the other methods. However, you will need to test it out with your database. Since it prints a separate report for every line of labels (using different where clauses), it can be time consuming. It has to constantly load the report writer code into memory and then look for the report. 2. CONTROL TABLE Method ------------------------ This method is detailed with step-by-step instructions in the article, "Multi-Up Labels: Control Table Method" in this issue of the BBS EDITORIAL. It uses the report writer, a small (single column) projected table (that is used to "control" the process), and simple programming (to number the rows and project the control table). This method requires a small temporary table which will have to be removed at the end of the process. It can be quite fast and it is simple to use once the report has been set up. 3. LABELS.MAC Method --------------------- You will find the LABELS.MAC program on your R:base RBEDIT... utility disk. It is well documented with multiple comments and it is a real R:base 5000 program. If you are not a programmer, you may have difficulty understanding it. LABELS.MAC uses programming and variables exclusively. This method does not use the report writer and does not require a temporary table. It can be slower than the other methods unless you send output to a file, run it overnight, and then print the labels in the morning using the DOS PRINT command on the file. Its advantages include the ability to go wider than 131 characters (you can go as wide as 256). See the article, "Programming Tips" in this BBS EDITORIAL for information on a program named NOBLANKS.LAB (a modified version of LABELS.MAC) that removes blank lines when there is no company or second address line. LABELS.MAC provides examples of all of the following programming methods: o Passing parameters to an R:base program o Using IF blocks to do error trapping o Using the SET POINTER command, its error variable (named FOUNDROW), a WHILE loop, and the NEXT command to go row by row through your data in your R:base table and load the values of the columns into variables o Working with variables in combination with the SET and SHOW commands o Using the MOVE command LABELS.MAC will also give you experience in modifying existing R:base code so that it will work with your database and your particular column names. 4. PRINT TABLE Method ---------------------- This method uses a long command file (PRINTTBL.LAB) that can be downloaded from the FILES section. The PRINT TABLE method uses the program PRINTTBL.LAB, the report writer, and a temporary "print" table to print the labels. The temporary table used by this method is large; in fact it will take up as much room as the original table. After the table is built (which can be done overnight) this method is very fast. After printing the labels you will need to REMOVE the print table and then PACK or RELOAD your database (which can take a while if your database is large). Essentially, this method builds another table with extra columns for each of the labels across. In other words, the PRINT table for three-up labels there would have the following columns in it: NAME1 NAME2 NAME3 COMPANY1 COMPANY2 COMPANY3 and so on. After loading the print table the report prints very fast because no lookups are required at all. You simply locate each of the print table columns in your report and your labels are printed with lightning speed. Another advantage to the PRINT TABLE method is that it can be used with either R:base 4000 (version 1.1 or higher) or R:base 5000 (any version). ********************* MULTI-UP LABELS: SIMPLE LOOKUP METHOD ********************* Read the article, "Multi-Up Labels" (in this EDITORIAL) before continuing. "Multi-Up Labels" will introduce you to four different methods for printing multiple across labels. The SIMPLE LOOKUP method, presented here, is but one of them. The SIMPLE LOOKUP method requires only simple command files and the report writer. First, you need a column (ROWNUM) to hold the row number for every row in the table that holds your mailing label information (names, addresses, etc.). STEP 1. Use the EXPAND command to create a column named ROWNUM with an INTEGER datatype. STEP 2. Use the RENUMBER.CMD command file (listed below) to number the rows in the sort order you want. Notice the ...SORTED BY ZIP clause in the SET POINTER... command. By simply changing this ...SORTED BY clause you can number the rows in any sort order that you choose. For example, you might want to change the SORTED BY... clause on the SET POINTER... command to ...SORTED BY DEPT ZIP if you wanted your labels to print in zip order by department. This command file assumes that your table name is LABELS. If your name and address data is in a table with a different name, you need to change the word LABELS in the SET POINTER... command line to the actual name of your table. RENUMBER.CMD ------------ SET VARIABLE CNT = 1 *(initializes the variable "cnt" as the counter) SET POINTER #3 E3 FOR + LABELS SORTED BY ZIP *(sets up a route through the table in sorted order) WHILE E3 = 0 THEN *(sets up while loop. "While there are rows to process") CHANGE ROWNUM TO .CNT IN #3 *(changes the column "rownum" to current value of the variable "cnt") SET VARIABLE CNT TO .CNT + 1 *(adds 1 to the counter) NEXT #3 E3 *(Points at next record in the sort order) ENDWHILE STEP 3. Now you need to set up the report (named LABELS in this example) that will print a set of three across labels. To do this, you need to set up report variables which look up the second and third rows in the base table by using the ROWNUM column. You are looking up the second and third label in the same table. Setting up the report is a simple three-step process. First you need to understand how the report variables are used to look up the second and third across labels. Study figure 1 and the associated comments. --------------------------------------------------------------------------- FIGURE 1 - SAMPLE REPORT VARIABLES TO "LOOK UP" THE 2ND AND 3RD LABELS --------------------------------------------------------------------------- rn = rownum *(gets current rownum) rn2 = rn + 1 *(adds 1 to current row number counter) name2 = name in table where rownum = rn2 *(this is actually looking forward one row to the next name) rn3 = rn + 2 *(adds 2 to current row number counter) name3 = name in table where rownum = rn3 *(this is actually looking forward two rows in the sorted order) --------------------------------------------------------------------------- Still in (d)efine mode, you need to also look up the COMPANY, ADDR1, ADDR2, and CTSTZIP columns in a similar fashion. When you are finished, the definition section of your report will be similar to what is listed in FIGURE 2 assuming your table is named LABELS, and the columns in the table are: NAME, COMPANY, ADDR1, ADDR2, CTSTZIP: --------------------------------------------------------------------------- FIGURE 2. THE COMPLETE SET OF REPORT LOOKUP VARIABLES --------------------------------------------------------------------------- rn = rownum rn2 = rn + 1 name2 = name in labels where rownum = rn2 company2 = company2 in labels where rownum = rn2 addr12 = addr1 in labels where rownum = rn2 addr22 = addr2 in labels where rownum = rn2 ctstzip2 = ctstzip in labels where rownum = rn2 rn3 = rn + 2 name3 = name in labels where rownum = rn3 company3 = company3 in labels where rownum = rn3 addr13 = addr1 in labels where rownum = rn3 addr23 = addr2 in labels where rownum = rn3 ctstzip3 = ctstzip in labels where rownum = rn3 --------------------------------------------------------------------------- We recommend that you clear all your global variables before running this report in order to free up as much memory as possible. These lookups are different than those done in the Control Table method. Here, the lookups are done based on the value of a row number column. In the Control Table method, the lookups are done based on a COUNT that the Report Writer does during processing. In other words, the Report Writer counts the number of rows in the Control Table. After defining all the variables in (d)efine mode, locate the columns (for the first-across label) and the variables (for the second-across and third- across labels) in the (l)ocate mode. When you finish, the located items will look like one set of three-across labels. As the last report set-up step, (m)ark 6 lines of detail in the (m)ark mode. STEP 4. Finally, you need a short command file to control the whole thing and print the report for every group of three rows in the table. The command file in figure 3 will accomplish this for you. --------------------------------------------------------------------------- FIGURE 3. --------------------------------------------------------------------------- SET POINTER #3 E3 FOR LABELS SORTED BY ROWNUM *(sets the path through the table in order of the row numbers.) WHILE E3 = 0 THEN SET VAR REPNUM TO ROWNUM IN #3 *(gets the row number needed for the report) PRINT LABELS WHERE ROWNUM = .REPNUM *(prints the LABELS report for each 3rd row as sorted) NEXT #3 E3 *(increments the row pointer) NEXT #3 E3 *(increments the row pointer) NEXT #3 E3 *(increments the row pointer) *(now the row pointer is pointing at row #4 which will become the first label in the next group of three for the next report) ENDWHILE --------------------------------------------------------------------------- ******************** MULTI-UP LABELS: CONTROL TABLE METHOD ******************** Read the article, "Multi-Up Labels" (in this EDITORIAL) before continuing. "Multi-Up Labels" will introduce you to four different methods for printing multiple across labels. The CONTROL TABLE method, presented here, is but one of them. STEP 1. Make sure that the table containing the names and addresses has a ROWNUM column with consecutive integer row numbers. If you do not already have this, see the article, "Multi-Up Labels: Simple Lookup Method" in this EDITORIAL for a short explanation. If you need a more detailed explanation, see the article, "Auto Numbering" in the November 85 EXCHANGE. For fastest speed the ROWNUM column should be the first column in the table and it should be keyed. Be certain that the row numbers are consecutive. The rows DO NOT have to be in a physically sorted order, but there must not be any gaps in the numbers. In the example below, the column on the left is fine but the column on the right will not work because the number 2 is missing. ROWNUM ROWNUM ------ ------- 1 1 3 4 4 5 2 3 If you have deleted rows since the last time you numbered your rows, you will want to renumber them. STEP 2. Project any single column of the address table to a new table. It does not matter what column you use because it is the number of rows in the projected table (named CONTROL in this example) that controls the processing, not the value of its column. It would be best to avoid DOLLAR columns and TEXT columns (longer than four characters), because of the need to conserve space. The projected table becomes a "control" table against which the mailing label report will be run. It controls the number lines (or groups) of multi-up labels that will be printed. Compute the correct number of rows to project by using the following formula: Divide the total number of rows in the name and address table (named LABELS in this example) by the number of labels you will be printing across the page (three in this example) and discard the remainder. Finally add one to the result. If there are 2,354 rows in LABELS, and you want to print three-up labels, the answer to the calculation is (2,354 / 3) + 1 or 785. The project command to use in this case would be: PROJECT CONTROL FROM LABELS + USING ROWNUM WHERE LIMIT = 785 You had to add one in order to make sure the last line (or group) of multi- across labels will be printed. It may have been truncated away by the division if there was a remainder after doing the division. The number of rows in the CONTROL table controls the number of lines (or groups) of multi-up labels that will be printed. For another example, if your name and address data is in a table named LABELS, and the row number column is ROWNUM, the following segment of code will create a CONTROL table with the correct number of rows: . . . SET V UP INTEGER SET V TOT INTEGER WRITE "How many labels + across?" FILLIN UP USING + "ENTER 3 FOR 3-UP, + 4 FOR 4-UP, ETC." COMPUTE NUM AS MAX + ROWNUM FROM LABELS SET V TOT TO .NUM / .UP SET V TOT TO .TOT + 1 PROJECT CONTROL FROM LABELS + USING ROWNUM + WHERE LIMIT = .TOT . . . The projected table CONTROL will be the base table for the MULTI-UP label report and the LABELS table will be the lookup table. STEP 3. Using REPORTS, create a report that uses CONTROL as its base table and does "lookups" against the LABELS table. The report should pull in the name, address, and all other information to be printed, and house that information in report variables. The report will do the lookups based on a report variable (named RCOUNT in this example) which is incremented by one for each row in the CONTROL table. The report will perform the lookup based on the RCOUNT variable. RCOUNT will be used to lookup the appropriate ROWNUM in the LABELS table. The report creation step has 6 mini-steps: o Name the report (MULTI-UP in this example) o Name the table (CONTROL in this example) o (S)et pagesize to 0 (zero) o (D)efine all the lookup variables (example below) o (L)ocate all the lookup variables o (M)ark all lines as (d)etail Figure 1 below is an example of what the defined block of report variables in the report might look like for three-up labels: --------------------------------------------------------------------------- FIGURE 1. DEFINING THE LOOKUP VARIABLES --------------------------------------------------------------------------- R1COUNT = R1COUNT + 1 V1NAME = NAME IN LABELS WHERE ROWNUM = R1COUNT V1ADDR1 = ADDR1 IN LABELS WHERE ROWNUM = R1COUNT V1ADDR2 = ADDR2 IN LABELS WHERE ROWNUM = R1COUNT V1CTSTZP = CTSTZP IN LABELS WHERE ROWNUM = R1COUNT R2COUNT = R1COUNT + 1 V2NAME = NAME IN LABELS WHERE ROWNUM = R2COUNT V2ADDR1 = ADDR1 IN LABELS WHERE ROWNUM = R2COUNT V2ADDR2 = ADDR2 IN LABELS WHERE ROWNUM = R2COUNT V2CTSTZP = CTSTZP IN LABELS WHERE ROWNUM = R2COUNT R3COUNT = R2COUNT + 1 V3NAME = NAME IN LABELS WHERE ROWNUM = R3COUNT V3ADDR1 = ADDR1 IN LABELS WHERE ROWNUM = R3COUNT V3ADDR2 = ADDR2 IN LABELS WHERE ROWNUM = R3COUNT V3CTSTZP = CTSTZP IN LABELS WHERE ROWNUM = R3COUNT R1COUNT = R3COUNT --------------------------------------------------------------------------- (L)ocate the lookup variables (V1NAME, V1ADDR1, V1ADDR2, etc.) across the page in the appropriate groups (one group for each label). Because you are using the R:base 5000 report writer you are limited to label stock that is 131 character positions wide (or less). Make sure that the ROWNUM column in the LABELS table is the first column defined, and that it is keyed (use the BUILD KEY command to key it). **************** PROGRAMMING TIPS **************** Product: R:base 5000 version 1.01 or higher This article is based on two R:base 5000 programs by Mark Rasmussen Washington, D.C. Many programmers have come across the need for something analogous to subscripted variables. A subscripted variable, as used here, means a variable (such as LNCOUNT) that contains another variable name with a number tacked on to the end of it (such as LINE3). Creating the subscripted variables is simply a matter of concatenating a number onto the end of any variable name that you choose. For example, if you concatenate the value of an INTEGER variable named SUBSCRPT onto the end of the literal word "LINE", put the result into a variable LNCOUNT, and then increment SUBSCRPT each time through a WHILE loop, the variable LNCOUNT will end up containing the following subscripted variable names: LINE1, LINE2, LINE3, LINE4, etc. at different times. In other words, the first time through the WHILE loop, LNCOUNT equals LINE1, the second time through, LNCOUNT equals LINE2, and so on. All this may seem confusing until you actually study the two R:base 5000 programs (NOBLANKS.LAB and TBL-MENU.MAC) that take advantage of this logic. Both of these files can be downloaded from the application area of the FILES section of the BBS. NOBLANKS.LAB ------------ NOBLANKS.LAB uses subscripted variables to insure that blank lines don't show up in labels when, for example, ADDR2 is blank (in other words, contains a NULL value) or the COMPANY is blank. The subscripted variable (LNCOUNT) is used to store variable names (LINE1, LINE2, ... LINE6) which in turn contain the lines for the labels. NOBLANKS.LAB works for one-up or multiple-up (such as three across, four across, etc.) labels. It is a modified version of the LABELS.MAC program (which prints all the blank lines) located on your R:base 5000 RBEDIT... utility disk. TBL-MENU.MAC ------------ TBL-MENU.MAC is an example of a macro that uses subscripted variables to make and use a "table generated menu". The "picks" in a table generated menu come from actual values in an R:base table. In other words the values in the table create the menu. In TBL-MENU.MAC, a subscripted variable (WHVAL) contains variable names (WHVAL0, WHVAL1, WHVAL2, ... WHVAL9) which in turn contain the values that were "picked" by the operator and are to be used in a WHERE clause (PRINT rptname WHERE... for example) as follows: ...WHERE DESCRPT CON .WHVAL1 OR + DESCRPT CON .WHVAL2 OR + DESCRPT CON .WHVAL3 The values in WHVAL1, WHVAL2, etc. are picked by the operator from a list supplied by an R:base table and are then loaded into subscripted variables to be used by the WHERE clause. This makes it possible to use a constantly changing set of picks. You need to initialize all the WHVALn variables (where n is the subscript) to a value that you know is not in the table that the WHERE clause will be acting upon. For example, TBL-MENU.MAC uses ZZZ as the initial value. Otherwise, if the operator picks less than 10 items, you will get an error. Or if the value does exist, rows of data will be included that the operator did not pick. In the TBL-MENU.MAC program, the menu of choices is generated by the following command: EDIT KEYWORD FLAG FROM KEYLIST where KEYLIST is the table that holds the menu picks. To "pick" from this menu, the operator puts an X in the FLAG column next to the KEYWORDs desired. The Xed KEYWORDs are then loaded into the WHVALn variables and used in a WHERE clause. Table generated menu picks are useful in almost all types of applications but particularly for: o Library, Employment Matching, Project Management, and other applications that make extensive use of KEYWORD searches where you want to list the keywords and have the operator choose which ones to use. You want the menu to be "table generated" because the keywords are constantly changing and the operator can more easily change the values in a KEYWORDS table than change an application menu. o Accounting and Financial applications where the set of accounts and customer numbers (that you may be picking from) change over time. o Applications where you want the operator to review a list of items that are a close match and then pick the one that "wins". For example you might want to list all the names starting with JO because you are not sure whether it was input as JOE, JOSEPH, JO, JOIE, JOSIE, or JOSEPHINE; then when you see the list you will be able (based on other info in the list) to pick the winner or winners which will then be used in the WHERE clause for the actual report. *********************** MAKING R:BASE GO FASTER *********************** by Jim Browne Systems/Communications Manager Diocese of Colorado Springs Colorado Springs, CO Some of you who are running R:base 5000 on the IBM PC-AT may have been reading about changing the speed of your PC-AT by replacing the original 6.0 mhz CPU crystal with a "faster" one. This past summer, I replaced the original crystal in my PC-AT with one that is faster. The crystal I used is rated at about 16 mhz which, I am told, nets a new CPU clock speed of about 8.0 mhz. The results were very rewarding while running R:base 5000. If you check the back of just about any PC publication these days, you will find a variety of third party vendors now offering these faster crystals. For about $130.00, one vendor now sells a a kind of "dial-a-speed" five speed crystal that allows the user to literally dial up to five different speeds from the back of the PC-AT. The idea is that you push up the speed of the 80286 chip in the PC-AT until your software will not run. The major improvement for my R:base application (a large mailing list/fund raising system) was noticed in execution time of WHILE loops. Just about all my command files that had WHILE loops ran about 20 percent faster. The total print time of reports was cut by about 15 to 20 percent. I saw very little improvement, however, in command files that involved LOAD commands. Screen I/Os improved somewhere in the range of 10 to 15 percent. As it turns out, the process of changing an PC-AT crystal is quite simple. The crystal I installed was obtained from Emerald Systems Corp. 4901 Morena Blvd., San Diego, CA 92117 - (619) 270-1994 and came complete with installation instructions that were very easy to follow. The entire process took about 35 minutes from start to finish. CAUTIONS: -------- There are some cautions to consider. IBM and their authorized dealers will tell you that the crystal swap may void your warranty. IBM has recently come under some criticism for designing into the PC-AT a "socketed" crystal and then not allowing owners to change crystals. They say you change the crystals at your own risk. Also you cannot swap crystals on the new PC-AT with the 30 megabyte hard disk. Like many IBM users of PC products, I am more interested in increasing the speed of my computer without destroying my application software than in warranty concerns. As of this date, I am successfully running: o Microrim's R:base 5000 o Lotus Development Corporation's Symphony o Microsoft Word and o RoseSoft's Prokey on my PC-AT with a 60 megabyte high speed (28 ms access time) disk drive supplied by Emerald Systems Corporation. I use an AST Advantage! memory board with Hercules Computer Technology's color graphics card and the IBM color monitor. I have used Okidata 2410 Pacemark, Epson LQ1500, and FX185 printers with the system and have experienced no problems. Most articles on this subject now say the 8.0 mhz crystal has no effect on software. Crystals that run at higher speeds (20 mhz, for example) do have problems. Since the crystals only cost about $10.00 to $15.00 each, you simply buy a few at different speeds and test them out. If you go faster than 8 mhz, I strongly recommend you build a test database before you try it out. An extra backup of your hard disk would also be smart before you "blast off". OTHER CONSIDERATIONS: -------------------- The actual improvements you will see while using a faster crystal in your PC-AT will depend on several factors. We routinely print 15,000 mailing labels twice each month using R:base. In an effort to improve the speed of this process, we were careful to invest in other specialized equipment that complements the faster crystal speed that the PC-AT can utilize. First, consider the speed of your disk drive. The standard PC-AT disk drive is rated at about 40 ms average seek time (the time needed to "lock on" to a record in the database) The drives we use are about 30% faster than IBM's in average seek time. The data transfer rate of our drives are also about 1.5 to two times faster than the standard IBM PC-AT's. The data transfer rate helps improve the speed in which we can transfer files from memory to the hard drive. Hard drives that exceed the performance of the IBM PC-AT standard 20 megabyte drives are available from several vendors. We purchase our high speed disk drives from Emerald Systems. Emerald, a supplier of high capacity (up to 240 meg) hard drives used in LANs, PCs and PC-ATs, often suggests the crystal swap to PC-AT users. For you "power" users of R:base 5000, you might notice that all hard drives sold by Emerald also have the ability to exceed the 32 megabyte maximum file size that DOS is limited to. Try projecting a new table in R:base that causes your *2.rbs file to exceed 32 meg. It will not run unless your drives can handle files larger than 32 meg. We also have improved R:base efficiency by printing our large reports (like the mailing label report) to a disk file. We never print these long reports to the printer. Our 15,000 cheshire mailing labels (printed four across) take about four hours even with our equipment. We print these to a disk file at night and use the DOS PRINT command to run the labels the next day. The DOS PRINT command allows us to print the labels in background mode while we access R5K simultaneously from the same PC-AT. Finally, be sure you have set up the correct CONFIG.SYS file for your PC. If you have forgotten to include the Buffers=20 command in the CONFIG.SYS file (like I did!), do so immediately. The improved speed is dramatic. Try altering the number of buffers until you achieve the best performance. *********************** REAL NUMBERS IN REPORTS *********************** Product: R:base 5000 version 1.01 Page 11-55 of the R:base 5000 version 1.01 manual explains how to align the decimal points of real numbers in REPORTS by specifying the position of the decimal point in (E)dit mode. The bottom of page 11-55 explains that R:base will attempt to format your REALs the way you want them, but if the number is too large (scientific notation format) or if the accuracy of the number is compromised, it reverts to the original real number format. Review page 11-55 and then take a look at the table in figure 1 which shows you exactly what all this means in terms of your numbers and how they are going to look. ---------------------------------------------------------------- Figure 1. Aligned Real Numbers ---------------------------------------------------------------- A. B. C. D. E. F. UNALIGNED R:BASE'S ALIGNED ALIGNED ALIGNED ALIGNED ACTUAL ORIGINAL W/DECIMAL W/DECIMAL W/DECIMAL W/DECIMAL REAL NUM REAL NUM IN SECOND IN THIRD IN FOURTH IN FIFTH DESIRED FORMAT POSITION POSITION POSITION POSITION ---------- --------- ---------- ---------- ---------- ---------- 1) 0.123467 0.12347 0.1 0.12 0.123 0.1235 2) 1.234567 1.23457 1.2 1.23 1.235 1.2346 3) 12.34567 12.3457 12.3 12.35 12.346 12.3457 4) 123.4567 123.457 123.5 123.46 123.457 123.457 5) 1234.567 1234.57 1234.6 1234.57 1234.57 1234.57 6) 12345.67 12345.7 12345.7 12345.7 12345.7 12345.7 7) 123456.7 123457. 123457. 123457. 123457. 123457. 8) 1234567 .123457E7 .123457E7 .123457E7 .123457E7 .123457E7 9) -.1234567 -0.12346 -0.1 -0.12 -0.123 -0.1235 10) -1234567 -.123457E7 -.123457E7 -.123457E7 -.123457E7 -.123457E7 11) -123.4567 -123.457 -123.5 -123.46 -123.457 -123.457 12) 123.000 123.000 123.0 123.00 123.000 123.000 13) 000.123 0.12300 0.1 0.12 0.123 0.1230 ---------------------------------------------------------------- First, look at lines 8 and 10. These two numbers have over six digits to the left of the decimal point and so they are converted to scientific notation when put into R:base real number format. Attempts to align them in columns C, D, E, and F fail because they are in scientific notation. Next, look at column F in the table. Although lines 4, 5, 6, 7, 11, and 12 have decimal points that appear to be in the wrong place, this is not a bug. It was necessary in these cases to revert to the original format in order to show the true value of the number. You can see that column F matches column B (the original format) in each of these cases. The same thing has happened in all the other cases where the decimal point appears to be in the wrong place. You can predict when this will happen by applying the following general guidelines: 1. The largest location size in REPORTS for REAL numbers is ten positions. 2. Of the ten, six of the positions are for the real number significant digits and one is for the decimal point. 3. The other three positions are only used by scientific notation and/or the minus sign. 4. If there are more than six significant digits to the left of the decimal point, then the real number is converted to scientific notation. 5. Finally, if there are less than six digits to the left of the decimal point, compute the total number of significant digits. In other words, add the number of significant digits that are on the left of the decimal point to the number of significant digits on the right. If the result is six or higher, all the significant digits to the left will be printed, followed by the decimal point, and then the digits to the right are rounded to fit in the remaining space. This rule overrides any decimal point alignment you have defined in REPORTS. This is why it appears that the decimal points are not aligning the way you want them. For examples of this last guideline in action, look at the following REAL numbers in figure 1: COLUMN C - LINE 7 COLUMN D - LINES 6 AND 7 COLUMN E - LINES 5, 6, AND 7 COLUMN F - LINES 4, 5, 6, 7, 11, AND 12 If you are having trouble aligning your decimal points because of this last guideline, but you ARE able to print without converting to scientific notation, then you may want to read, "Aligning Decimal Points in a Report", in the September 1985 EXCHANGE. The concepts in the September article should work even if you are on the "outer limits" because it converts the REAL number to a TEXT datatype before printing it. ******** HOT TIPS ******** Here are solutions and explanations for some common problems. =========================================================================== R:BASE COMPATIBILITY =========================================================================== Product: R:base 5000 all versions DESCRIPTION: How can I tell whether R:base 5000 will run on my IBM compatible computer. It is not 100%, but close to it. What are some tests? SOLUTION: We are not able to guarantee that if your compatible passes these tests, you will never have problems; however, the chances are good. Tests include: o You are able to bring R:base 5000 up on your computer and everything looks normal o Color works o The F1 key adds a line and the F2 key deletes a line in RBEDIT o The F3 and F10 keys work as expected in EXPRESS o The R:base 5000 tutorial works on your computer o For R:base 5000 Multi-User, the evaluation copy works on your computer and LAN system =========================================================================== FORMS AND REPORTS =========================================================================== Product: R:base 5000 all versions DESCRIPTION: I removed a column from a table and now I cannot get into my data entry forms for this table. I realize that the LAYOUT is incorrect, but FORMS refuses to bring up my form so that I can correct it. What do I do? EXPLANATION: Although the column no longer exists, it is still located in the form. You need to remove the location of the column from your form by using the EDIT command. SOLUTION: Replacing "formname" with the actual name of your form, enter the following command at an R> prompt: EDIT ALL FROM FORMS + WHERE FNAME = formname Press the down arrow key until you find the word LAYOUT in the second column (FDATA) on your screen. Next, continue to slowly tap the down arrow key until you see the name of the deleted column in the second column (under the word LAYOUT). When your cursor is on that row (highlighting one of the columns), press the F2 key followed by the [ENTER] key to delete the row. Finally, escape from the EDIT and your form will now work because the location of the column that no longer exists has been removed. In other words, the LAYOUT has been corrected. This same general method will also correct your REPORTS that have this same deleted column located in them. In the case of REPORTS, the command to use is: EDIT ALL FROM REPORTS + WHERE RNAME = rptname If you have renamed a column and that column is located in FORMS or in REPORTS you can correct the LAYOUT section in your forms and reports by using the EDIT ALL FROM... command. In this case, however, you will NOT want to delete the applicable row. Instead, type over the "old" name with the "new" name and then press [Esc]. =========================================================================== RINGING THE BELL =========================================================================== Product: R:base 5000 all versions DESCRIPTION: I want to ring the bell and print my own error message on the screen based on my own error trapping routines. SOLUTION: Put the following block of code in your program when you want the bell to ring: . . . SET BELL ON SET ERROR MESSAGE ON OUTPUT TEMP.DAT2 RING MY CHIMES! SET ERROR MESSAGE OFF OUTPUT SCREEN WRITE "This is my error msg" PAUSE . . . Since RING is not an R:base command, the bell will sound and the error message will go into the file where the operator will not see it. The WRITE command will now print your error message on the screen. =========================================================================== ADDING LEADING ZEROS =========================================================================== Product: R:base 5000 all versions DESCRIPTION: I have a part number column that is defined as INTEGER, I changed it to TEXT using the CHANGE COLUMN... command and now all the numbers do not sort properly. EXPLANATION: TEXT type columns are sorted from left to right. This is not a bug; it is the way computers work with TEXT values. TEXT values are sorted as indicated below. Notice that both the numbers and the letters sort left to right. 2 B 200 BEE 3 C 37 CI 374 CID 38 CH Therefore, to solve your problem, all you need to do is put in leading zeros. With leading zeros the above numbers will sort as follows: 002 003 037 038 200 374 SOLUTION: To get your text values to sort the way you want them to, you need to put in leading zeros so that the "ones", "tens", "hundreds", etc. columns all line up. In this example: o The table is named PARTS. o The original INTEGER column (that you changed to TEXT) is named PNUM. o The new TEXT column (which will hold the new part number with the leading zeros) is named PART# o The largest integer part number in the table is four characters long which means no more than three leading zeros are required. Use the following code to change PNUM back to INTEGER and to create the new PART# column: *(first change PNUM back to integer) CHANGE COL PNUM IN PARTS + TO INTEGER *(next EXPAND the table to include a new TEXT column that will hold the TEXT part number with leading zeros) EXPAND PARTS WITH PART# + TEXT 4 Now, use the following macro to put leading zeros into the PART# column: *(now set up the WHILE loop) SET V RESULT TEXT SET POINTER #3 E3 FOR PARTS WHILE E3 = 0 THEN SET V VPNUM TO PNUM IN #3 *(add 3 leading zeros) IF VPNUM GE 0 AND + VPNUM LE 9 THEN SET V RESULT TO 000 + .VPNUM ENDIF *(add 2 leading zeros) IF VPNUM GE 10 AND + VPNUM LE 99 THEN SET V RESULT TO 00 + .VPNUM ENDIF *(add 1 leading zeros) IF VPNUM GE 100 AND + VPNUM LE 999 THEN SET V RESULT TO 0 + .VPNUM ENDIF *(load RESULT into PART#) CHANGE PART# TO .RESULT IN #3 NEXT #3 E3 ENDWHILE =========================================================================== RULES =========================================================================== Product: R:base 4000, 5000, 6000 all versions DESCRIPTION: My rules do not check the column values in the same order as they are entered. What is wrong? EXPLANATION: Rules are checked in the order the rules appear in the rules table, not in the order the column values are entered. If there are two or more mistakes in the data on a table form (which violate rules) the first message displayed will be that of the first rule, rather than the first piece of data entered. SOLUTION: Using the EDIT ALL... command, change the numbers of the rules into the order you want the rules to be checked. For R:base 4000 and 6000 the command to use is EDIT ALL FROM RBSRULES. For R:base 5000, use EDIT ALL FROM RULES. Next, unload the rules data into a file (replace "ruletbl" with RULES if using R:base 5000, otherwise use RBSRULES): OUTPUT TEMP.DAT UNLOAD ALL FOR ruletbl + SORTED BY NUMRULE OUTPUT TERMINAL Finally, delete the old rules table (RBSRULES or RULES) and create the new one: REMOVE ruletbl INPUT TEMP.DAT =========================================================================== BLANKING OUT A SCREEN LINE =========================================================================== Product: R:base 5000 all versions DESCRIPTION: In R:base 4000 I could set a variable to a blank (alt 255) and then SHOW this variable to blank out an entire line on the screen. This does not work in R:base 5000; only one character is blanked out. SOLUTION: Use the WRITE command to write a blank line to the screen, for example, WRITE " " AT 23 1 If you were going to blank out an entire line you would include 80 blanks between the quotes by using the plus sign to continue the blanks on the next line. R:base is smart enough not to include the plus sign as a literal character in the line. For example, the following would cause line 15 to be completely blanked out: *(20 blanks per line on four lines = 80 blanks total) WRITE " + + + " AT 15 1 Additionally, there is a macro available that will blank out an entire segment (or block) of the screen. You can download the BLANK.OUT file from the FILES section, application area. Download the file named MESS to see BLANK.OUT in action. MESS is a file with all the positions filled with letters. To see a blank section appear in the file, issue the following series of commands: NEWPAGE TYPE MESS RUN BLANK.OUT 5 20 4 30 **************** ACKNOWLEDGEMENTS **************** The following Microrim employees contributed programs, concepts, and/or articles for this EDITORIAL: MariEsther Burnham Kelly Cline Kay Dayss Marianne Jaeger Mike Johnson Paul Scheiner Liz Shattuck Richard Traband Mario Valverde TECHNICAL SUPPORT We provide a wide variety of technical support alternatives: o Direct line to technical support: (206) 641-7386. o Toll-free hotline available to all continental U.S. Software Maintenance Plan (SMP) subscribers outside Washington state. The toll-free hotline is a direct line to our senior technical support staff. o Electronic Bulletin Board (BBS): (206) 643-8545. o R:base EXCHANGE technical newsletter. Free to SMP subscribers and available by subscription to others (see coupon at the back of this issue). o We also have special support programs for Consultants, Application Developers, Value Added Resellers (VARs) and Dealers. To get information on how you can be a part of these programs, call (206) 641- 6619 and ask for the INFO center. We will send you an entire package of information. TRADEMARK R:BASE SERIES is a trademark of Microrim, Inc. Application EXPRESS is a trademark of Microrim, Inc. FileGateway is a trademark of Microrim, Inc. CLOUT is a trademark of Microrim, Inc. XRW is a trademark of Microrim, Inc. IBM is a registered trademark of International Business Machines Corp. XT and AT are trademarks of International Business Machines Corp. EPSON is a registered trademark of Epson America, Inc. LQ1500 is a trademark of Epson America, Inc. FX185 is a trademark of Epson America, Inc. Microsoft is a registered trademark of Microsoft Corporation Microsoft Word is a trademark of Microsoft Corporation Symphony is a trademark of Lotus Development Corporation Advantage! is a trademark of AST Research, Inc. Okidata is a trademark of Okidata Corporation EMERALD is a trademark of Emerald Systems Corporation Prokey is a trademark of RoseSoft, Inc. Hercules Computer Technology is a trademark of Hercules Computer Technology DISCLAIMER Microrim, Inc., makes no representation or warranties with respect to the contents hereof, and specifically disclaims any implied warranties of merchantability or fitness for any particular purpose. Further, Microrim, Inc., reserves the right to revise this publication and to make changes in the content hereof without obligation to notify any person of such revision or change and shall not be liable for errors contained herein or for incidental or consequential damages in connection with the furnishing, performance, or use of this material. tech notes 9 ******************** * VARIABLE FORMS * ******************** In response to your requests for further information on variable forms, we are including the following five articles in this section: o Why or When to Use Varforms o Varform Basics & Two-Page Forms o Putting "Display Only" Info on a Varform o Using Rules With Varforms o Loading Several Rows From One Varform *************************** WHY OR WHEN TO USE VARFORMS *************************** Product: R:base 5000 all ver There are a number of reasons to use variable forms instead of tableforms including: o For multi-page forms. o To load data, entered on one form, into more than one table. o To load data, entered on one form, into more than one row of a table. o When you want to display some data on a data entry screen (form) that cannot be edited. o When lookups are desired. In other words, you want the operator to enter the Customer's number and have R:base lookup and display the Customer's name. o When calculations are needed on what the operator has entered before the data is loaded into the table. o Anytime you want to load information directly into variables and you do not want to use a whole series of FILLIN command lines. ******************************* VARFORM BASICS & TWO-PAGE FORMS ******************************* Product: R:base R:base 5000 all ver BASIC DATA ENTRY WITH A VARFORM: ------------------------------- The code for a basic variable form (with three variables VAR1, VAR2, and VAR3) being loaded to a three column (COL1, COL2, and COL3) table is listed below. Note that it is assumed you have defined the variable form (VFORM in this example) using the FORMS command prior to running this code. LABEL ENTFORM SET NULL " " NEWPAGE WRITE "Press [ESC] to add this + row or [PGDN] to quit" AT 1 1 DRAW VFORM EDIT VAR RETURN ESC PGDN IF #RETURN = ESC THEN GOTO LOADBLK ENDIF IF #RETURN = PGDN THEN QUIT ENDIF LABEL LOADBLK SET NULL -0- LOAD tblname .VAR1 .VAR2 .VAR3 END CLEAR VAR1 CLEAR VAR2 CLEAR VAR3 GOTO ENTFORM Notice that there are threee basic blocks of code. o The first block accomplishes data entry (with the DRAW and EDIT VAR commands and then passes control based on the RETURN key (ESC or PGDN) that the operator chooses. NULL is set to a blank so the operator will not see the -0- on the screen for the NULL variables. o The load block is the block that actually loads the values stored in the variables into the form. Until this block is executed, no data that the operator entered is saved. NULL must be set to -0- for the LOAD to work correctly. If it is set to a blank, you may get a kind of blank (ALT 255) entered into your table when you wanted a NULL. o The final block clears the variables and goes back to the top for the next form. BASIC EDITING OF EXISTING DATA: ------------------------------ It is also possible to edit existing data using a variable form. First, use the SET POINTER command to point at the row you want to edit. Next, use the SET VAR command to pull the data (from the columns in the row you are pointing at) into variables. Next, edit the variables with the DRAW and EDIT VAR commands. Finally, write the new values back to the table with the CHANGE command. For example: LABEL TOP FILLIN VID USING "Enter the ID + number for the record you want + to edit or Q to quit: " at 1 1 IF VID NE Q THEN SET POINTER #3 E3 FOR tblname + WHERE ID EQ .VID SET VAR VAR1 TO COL1 IN #3 SET VAR VAR2 TO COL2 IN #3 SET VAR VAR3 TO COL3 IN #3 NEWPAGE WRITE "Press [ESC] to make + changes or press [PGDN] to + bail out" AT 1 1 DRAW VFORM WITH ALL EDIT VAR RETURN ESC PGDN IF #RETURN = ESC THEN GOTO LOADBLK ENDIF IF #RETURN = PGDN THEN QUIT ENDIF LABEL LOADBLK SET NULL -0- CHANGE COL1 TO .VAR1 IN #3 CHANGE COL2 TO .VAR2 IN #3 CHANGE COL3 TO .VAR3 IN #3 CLEAR VAR1 CLEAR VAR2 CLEAR VAR3 ENDIF IF VID = Q THEN QUIT ELSE GOTO TOP ENDIF ENTERING TWO-PAGE VARFORMS: -------------------------- Variable forms are also easily demonstrated using multiple page form emulation as an example. There is a command file in the R:base 5000 manual on pages 15-44 and 15-45 giving one example of how to do multiple page form emulation with variable forms. The macro presented here (see Figure 1 is a more basic method that does not require keeping track of page numbers. Before running the macro you need to use the FORMS command to create the two variable forms FORM1 and FORM2. Note that in this example, no data is loaded into the table, until form2 has been entered. If the operator presses [Esc] after entering information into form1, the form1 information will not be loaded. This prevents a partial record from being loaded. =========================================================================== Figure 1. BASIC TWO-PAGE DATA ENTRY WITH VARIABLE FORMS =========================================================================== LABEL FORM1 SET NULL " " NEWPAGE WRITE "Press [PGDN] to go on to page two -- [ESC] to quit" AT 1 1 DRAW FORM1 EDIT VAR RETURN ESC PGDN IF #RETURN = PGDN THEN ; GOTO FORM2 ; ENDIF IF #RETURN = ESC THEN; QUIT; ENDIF LABEL FORM2 NEWPAGE WRITE "Press [Esc] to add this two-page record + -- [PGUP] to go back to page one" AT 1 1 DRAW FORM2 EDIT VAR RETURN ESC PGUP IF #RETURN = ESC THEN ; GOTO LOADVARS ; ENDIF IF #RETURN = PGUP THEN ; GOTO FORM1 ; ENDIF LABEL LOADVARS SET NULL -0- *( The following three lines of code are known as the "LOAD block". If you need more than one line to list the variables, you can use the plus sign to continue on the next line) LOAD tblname .varname1 .varname2 .varname3 ... END CLEAR varname1 ; CLEAR varname2 ; CLEAR varname3 ; ... GOTO FORM1 =========================================================================== THE LOAD BLOCK: -------------- The LOAD block is required to load data to the table. Remember that the dotted variables listed in the LOAD block are the variables that you located in the variable forms FORM1 and FORM2. The variables must be listed (side by side on the line following the LOAD... command line) in the same order that their corresponding columns appear in the table being loaded. Data is not actually added to your table until the LOAD block is executed. Therefore, the operator can go back and forth between FORM1 and FORM2 as many times as desired and no data will be added to the table until [ESC] is pressed while the second form is being displayed. If you have more columns in your table than variables in your LOAD block you will need to use NULL place keepers. For example, if the table had these columns: NAME ADDRESS CITY STATE and your variable forms were using only the following variables: VNAME VCITY Your load block would look like one of the three examples presented below: *(LOAD BLOCK EXAMPLE ONE) LOAD tblname .VNAME -0- .VCITY -0- END Or it could look like this: *(LOAD BLOCK EXAMPLE TWO) LOAD tblname ; FILL .VNAME -0- .VCITY END Example two works because the FILL command, when included on the LOAD ... line with the semicolon, will fill all remaining columns (that is all the columns after the first three) with NULLs. A third type of LOAD block has a USING clause. For example, *(LOAD BLOCK EXAMPLE THREE) LOAD tblname USING NAME CITY .VNAME .VCITY END The USING clause does not actually load the data; you must still list the variables to be loaded on the line below the LOAD... command line. However, the USING clause does allow you to specify which columns are to be loaded and the order to be followed in loading them. Remember: o Make sure that NULL is set to -0- before executing a LOAD block o Specify column names in the USING clause of the LOAD... command line o Specify variable names (with dots in front of each one) on the line below the LOAD... command line. Each separate line, between the LOAD... line and the END line, will be loaded to a separate row in the table. Usually only one row is added at one time so the LOAD block is usually three lines long (LOAD..., dotted vars, and END). For an example of a LOAD block that loads more than one row, see the article, "Loading Many Rows From One Form" in this Technical Edtorial. o Make sure that the order (left to right) in which the column names are listed in the USING clause (on the top line) corresponds exactly with the order (left to right) in which the dotted variables are listed on the middle line. o End the LOAD block with END. THE RETURN CLAUSE: ----------------- Using the RETURN clause in the EDIT VAR ... command line and excluding ENTER as an option allows the operator to use the [ENTER] key to bounce from place to place in the form during data entry. If ENTER were to be used in the RETURN clause of the EDIT VAR..., the operator would have to use the arrow keys to bounce around in the form. You may want to add error trapping code to the basic code in Figure 1., but the basic concepts will hopefully help you to better understand variable forms. To use variable forms for data entry in an EXPRESS application, you would build a macro similar to that in Figure 1. and then pull it into your EXPRESS application by using the MACRO action pick. SUMMARY: ------- The basic macro presented in Figure 1. demonstrates the following basics of variable forms: o Use NEWPAGE to clear the screen before a DRAW. o Use WRITE to write a message on the first line telling the operator what to do when finished with the form. o Use DRAW to draw the variable form on the screen. o Use EDIT VAR to enter data into the form. o Use a RETURN clause on the EDIT VAR command to limit the possible values for the #RETURN system variable. o Use an IF block for every #RETURN possibility. o Use a LOAD block to load the variable values into the table. Be sure to set NULL to -0- right before the LOAD block and list the variables in the same order as the corresponding columns in the table. o After loading the variables clear each one individually with CLEAR varname command. o Use the FILLIN, SET POINTER, SET VAR, DRAW, EDIT VAR, and CHANGE commands to edit using a variable form. *************************** DISPLAY ONLY INFO ON A FORM *************************** Product: R:base 5000 all ver Before attempting this article, review the "Varform Basics & Two-Page Forms" article in this Technical Edtorial. By using variable forms, it is easy to display "information only" data on the screen in the same form being used for data entry. For example, when entering invoices for CUST# 234 you may want to enter 234 and have the computer display the CUSTNAME, CUSTADDR, etc. to make sure 234 is the correct number before entering the invoices for that customer. The computer can lookup the customer information to display it on the screen. PROCEDURE: --------- o Get the CUST# from the operator with a variable form or with the FILLIN command. o Using the SET VAR command lookup the values you need to display. o DRAW the form WITH ALL to show the values of all the variables. The ones you just looked up will be displayed. The ones to be entered will be blank. o Now use the EDIT VAR command and list the names of the variables in the form that are to be entered. EXAMPLE: ------- The following example code assumes that you have previously set up a variable form FORM1 with the variables VLOOK1, VLOOK2, VINV#, VINVAMT, and VCUST# located in it. It is also assumed that you have two tables, CUST (with columns CUST#, CUSTNAME, and CUSTADDR) and INVOICE (with columns CUST#, INV#, and INVAMT). . . . LABEL LOOKUPS CLEAR VAR VCUST# NEWPAGE FILLIN VCUST# USING + "Customer Number: " AT 1 1 SET VAR VLOOK1 TO CUSTNAME + IN CUST WHERE CUST# = .VCUST# SET VAR VLOOK2 TO CUSTADDR IN + CUST WHERE CUST# = .VCUST# WRITE "Press [Esc] to add this + invoice, [PgDn] for new + customer number, or [PgUp] to + quit" AT 2 1 LABEL INVOICES NEWPAGE DRAW FORM1 WITH ALL AT 3 EDIT VAR VINV# VINVAMT + RETURN ESC PGUP PGDN IF #RETURN = ESC THEN SET NULL -0- LOAD INVOICE .VCUST# .VINV# .VINVAMT END SET NULL " " CLEAR VINV# ; CLEAR VINVAMT GOTO INVOICES ENDIF IF #RETURN = PGDN THEN GOTO LOOKUPS ENDIF IF #RETURN = PGUP THEN NEWPAGE QUIT ENDIF . . . ************************* USING RULES WITH VARFORMS ************************* Product: R:base 5000 all ver Before attempting this article, review the "Varform Basics & Two-Page Forms" article in this Technical Edtorial. It is possible to use rules with variable forms. Basically, this method uses the following trick shots to make rules work with variable forms: o Draw the variable form at line three (or further down) on the screen to enable the error message from the rule to be displayed at the top of the screen and the instructions to the operator to be written on line two with the WRITE command. o Use the command CHECK (with a semicolon in front of it) on the "LOAD tblname" line in the LOAD block to turn rules on. The complete method is outlined in Figure 2. The method presented will work with single-page forms. If you are using multiple-page forms you will need to revise the method. The easiest way would be to load each page to a different temporary data entry table (TEMP1 and TEMP2) and repeat the unique identifying variable/column (such as ID). TEMP1 and TEMP2 will each have their own unique rules. When the data entry session is over, have your macro INTERSECT TEMP1 WITH TEMP2 FORMING TEMP3 and APPEND TEMP3 TO MASTER where MASTER is the name of the main table. Finally delete all the rows in TEMP1 and TEMP2 and remove TEMP3 so that you are ready for the next data entry session. You will also want to PACK or RELOAD to clean up your database, because you deleted rows and removed a table. =========================================================================== Figure 2. Using Rules With Variable Forms =========================================================================== *(initialize) NEW;SET MESS OFF;SET ERR MESS ON;SET BELL ON;SET NULL " " COMPUTE NEWTOT AS ROWS FROM tblname *(start here if last form entered did not violate rules) LABEL ADDREC NEW SET VAR OLD TO .NEWTOT CLEAR var1 ; CLEAR var2 ; CLEAR var3 WRITE "Press [Esc] to add; [PgDn] to quit" at 2,1 *(redo this section if rule was violated - this code allows 4 retrys only because of the limit of 5 DRAWs before a NEWPAGE) LABEL TRYAGAIN DRAW varform AT 5 EDIT VAR RETURN ESC PGDN *( next block of code will end the data entry session if operator chooses) IF #RETURN = PGDN THEN QUIT ENDIF *( next block of code attempts to add a row if no rules are violated ) IF #RETURN = ESC THEN SET NULL -0- LOAD tblname ; CHECK .var1 .var2 .var3 END SET NULL " " *( next block determines if rule was violated. If OLD = .NEWROWS then rule was violated, else a row was added and ready to add another row) COMPUTE NEWTOT AS ROWS FROM tblname IF OLD EQ .NEWTOT THEN GOTO TRYAGAIN ELSE GOTO ADDREC ENDIF ENDIF =========================================================================== You can also use your rules when editing using a variable form. You would need to make the following modifications to the code in Figure 2. o Add the FILLIN, SET POINTER, and SET VAR commands (see the "Basics... article) and other modifications necessary when editing existing data using a variable form. o Use the CHANGE command to load the data rather than a LOAD block. It is while the CHANGEs are being executed that the error messages will be printed on the screen. Issue a NEWPAGE right before the CHANGE command and DRAW the variable form enough lines down the screen to accomodate all the possible error messages. ******************************* LOADING MANY ROWS FROM ONE FORM ******************************* Product: R:base 5000 all ver Before attempting this article, review the "Varform Basics & Two-Page Forms" and the "Using Rules With Varforms" articles in this Technical Edtorial. Several customers have asked for the ability to load several rows at once from one form. For example, you may want to enter all the invoices for customer number 234 onto one form. You know that no more than three invoices would be entered at once, but sometimes you have only one or two to enter for a customer and you do not want NULL rows going into the table for the lines on the form that have no data. The method presented here is also very useful in applications like order entry where there may be several items ordered on one physical order form and you need each line to go into a separate row in the table. Using this method will save time. You do not need to wait for each invoice to be loaded into the table before entering the next one for the same customer. STEP ONE: -------- First determine the maximum number of lines to be entered at one time and create a variable form with a different variable for each "cell" (line / column combination) in your form. If you have a large number of variables, you may run out of memory and get an "Out of Dynamic Space" error message when trying to run your application. If this happens, and you already have 384K of memory available (the maximum R:base 5000 currently uses) you will need to abandon this method and go back to the "one line at a time method". If, however, you have 384K bytes of memory, you will not have any problem with an average number of variables. For example, the following scenario would be fine: o Basic Order Entry Application where each order can have a maximum of five parts ordered at once, but sometimes fewer parts are ordered. o If only two parts are ordered, for example, only two rows are to be added to the ORDERS table. We need to prevent NULL rows from being entered. o Variable form that has one master header line (to enter VCUST# and VORDDATE) and five transaction lines with three integer and dollar variables on each line. Each of the transaction lines will be loaded to a different row in the ORDERS table. o ORDERS table has five columns: CUST#, ORDDATE, PART#, QUANT, and COST. The variable form for this example would need to have the following 17 variables located in it: VCUST# VORDDATE VPART#1 VQUANT1 VCOST1 VPART#2 VQUANT2 VCOST2 VPART#3 VQUANT3 VCOST3 VPART#4 VQUANT4 VCOST4 VPART#5 VQUANT5 VCOST5 STEP TWO: -------- Create a rule that requires an entry in the PART# column of ORDERS. This rule will be used to prevent NULL rows from being added to the table if less than the maximum number of transaction records are included on a particular data entry form. STEP THREE: ---------- Put all the variables into the load block (the 1s on line one, the 2s on line two etc.) and include the VCUST# and VORDDATE on each of the lines. Each line will be loaded into a different row of the database. The complete code is listed in Figure 3. Do all of this with rule checking on and at the top of the load block enter the command: OUTPUT TRASHCAN this command will print all the error messages from the rules being broken into a file named TRASHCAN. At the bottom of the load block command file enter the following two commands to clear everything. OUTPUT TERMINAL ERASE TRASHCAN Now, if a rule is broken (meaning that nothing was entered on that line of the form) that particular row will NOT be added to the table. You will also want to have SET BELL OFF at the top of the command file. =========================================================================== Figure 3. Loading Many Rows From One Variable Form =========================================================================== NEW;SET MESS OFF;SET ERR MESS ON;SET BELL OFF;SET NULL " " LABEL TOP NEWPAGE WRITE "Press [Esc] to add; [PgDn] to quit" at 1 1 DRAW varform AT 2 EDIT VAR RETURN ESC PGDN IF #RETURN = PGDN THEN QUIT ENDIF IF #RETURN = ESC THEN SET NULL -0- OUTPUT TRASHCAN LOAD tblname ; CHECK .VCUST# .VORDDATE .VPART#1 .VQUANT1 .VCOST1 .VCUST# .VORDDATE .VPART#2 .VQUANT2 .VCOST2 .VCUST# .VORDDATE .VPART#3 .VQUANT3 .VCOST3 .VCUST# .VORDDATE .VPART#4 .VQUANT4 .VCOST4 .VCUST# .VORDDATE .VPART#5 .VQUANT5 .VCOST5 END OUTPUT TERMINAL ERASE TRASHCAN SET NULL " " ENDIF GOTO TOP ===========================================================================